pivot transform help

I need to transform the following layout by hopefully using the pivot transform, but am confused about the editor ......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 the pivot transform? Any suggestions?

The easiest way to accomplish this will be to transform the compound key into a single column key using the derived column transformation. You would then perform the pivot , then merge the dataflow back to include the compound key.

EDIT: It looks like you want the UNpivot transformation (that which takes you to a more normalized state). see http://technet.microsoft.com/en-us/library/ms141723.aspx

|||

Indeed the unpivot is exactly what I needed. Data flow is now as follows....

OLE DB selects the data

connect to Unpivot

Inside Unpivot editor, my PKcol1-PKcol3 above you check the pass-through box on the right. The other columns (col4-7) you check the other box. Then name the destination column, and put in values if you don't want the "Y" & "N".