confused about pivot

I need to transform the following layout by pivoting, but am confused ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.

I need to go from this...

PKcol1 PKcol2 PKcol3 col4 col5 col6 col7

A 2007 1 Y N N N

A 2007 2 Y Y N N

A 2007 3 N N N Y

into this....

A 2007 1 col4 Y

A 2007 1 col5 N

A 2007 1 col6 N

A 2007 1 col7 N

A 2007 2 col4 Y

A 2007 2 col5 Y

A 2007 2 col6 N

A 2007 2 col7 N

A 2007 3 col4 N

A 2007 3 col5 N

A 2007 3 col6 N

A 2007 3 col7 Y

Can I do this using PIVOT or should I just do 4 inserts (one for each col40col7) into a temp table? Any suggestions?

Give a look to UNPIVOT in books online; this is an UNPIVOT and not a PIVOT

|||

Kent is right, you need to use UNPIVOT.

select

PKcol1,

PKcol2,

PKcol3,

col,

[value]

from

dbo.t1

unpivot

(

[value]

for col in ([col4], [col5], [col6], [col7])

) as unpvt;

AMB