--====== TEST DATA SET-UP =================

if object_id('dbo.pvt') is not null

drop table dbo.pvt;

create table dbo.pvt

(

reckey varchar(10),

amt1 decimal(8,2),

amt2 decimal(8,2),

amt3 decimal(8,2),

amt4 decimal(8,2),

amt5 decimal(8,2)

);

insert dbo.pvt values ( 'ABCD1234', 44.92, 14.4, 22, 0, 11.52 );

insert dbo.pvt values ( 'ABCD5678', 3.12, 17.7, 0, 13, 100.5 );

/*

I'm trying to use the t-sql Unpivot statement to create this:

reckey amtnumber amt

ABCD1234 1 44.92

ABCD1234 2 14.4

ABCD1234 3 22

ABCD1234 4 0

ABCD1234 5 11.52

*/

--====== SOLUTIONS =================

-- SQL 2005: Unpivot the table.

SELECT reckey, amtnumber, amt

FROM

(SELECT reckey, amt1, amt2, amt3, amt4, amt5

FROM pvt) p

UNPIVOT

(amt FOR amtnumber IN

(amt1, amt2, amt3, amt4, amt5)

)AS unpvt;

-- SQL 2008: You can use Cross Apply:

SELECT reckey, amtnumber, amt

FROM dbo.pvt

CROSS APPLY (

VALUES (1, amt1)

,(2, amt2)

,(3, amt3)

,(4, amt4)

,(5, amt5)

) a(amtnumber, amt);