Pivot Key Values

Is it possible to use a expression as a pivot key value?

I need to take data from one table and pivot it into another but what pivots to where is conditional, so I'm wondering if it's possible to say something like

"where Column1 = 'A' and Column2 = 'B' " in the pivot key value.

I'm trying to get it to work but can't, just keep getting the 'No pivot Key found' error.

Thanks

bobbins,

How many conditions are there? You could just use a conditional split to grab the records with the correct values per type and use a seperate chain for each, doing a union all to get them back together after the chain.

--

Looking at it more closely you are probably trying to unpivot (un-normalize). You could possibly use an expression to do the trick. The expression is located on the data flow task and is referenced in a manner similar to Unpivot.Unpivot Input.ColumnName.PivotKeyValue . (NOTE: both pivot and unpivot expose the pivotkeyvalue as data flow property expressions) Although, to be perfectly honest, I'm not sure if you will have access to the record information going through the pipe at the moment or not, so that might not work either...

|||You could pivot the values with a script. Using the script allows you a lot more flexibility in defining the pivoting rules.|||

Thanks for the replies, I am trying to de-normalize the data, here is a better explanation of what I'm trying to do:

My source data is an Ingres db on Unix:

MemberID Date Value Code Type 5029348 01/12/2006 79 A 1 5029348 01/12/2006 8 B 2 5029348 01/12/2006 4 C 3

I want to put the data in a de-normalized table so it looks like this:

MemberID Date ValueA1 ValueB2 ValueC3 5029348 01/12/2006 79 8 4

So my data mapping rules are:

Where Code = A and Type = 1 then map to ValueA1

Where Code = B and Type = 2 then map to ValueB2

Where Code = C and Type = 3 then map to ValueC3

I would like to do this as the data comes through the pipe instead of creating a staging table at either the source end or destination and then just bumping the data straight in from the staging table. I have limited experience with SSIS and want to learn but I'm struggling to work out what to do in the time I've been given to do this, hence my question about what you can actually put in the Pivot Key Values. A conditional split will split the values out but how do I put them all back together again as one row per MemberID to go into the destination? Or is there another way to do this?

Thanks again

|||

Take a look at this post. http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

You'll have to alter the script to not use the Split function, and apply your mapping rules, but it should provide a good starting point. If you are still having problems, post back here and we'll help.