Pivot Enterprise Stage - transforming columns to single line

[TonyInFrance]

Morning / afternoon / evening ladies and gentlemen

As stated in the subject line, I have a table of around 1000 values for a composite key. This looks somewhat like this:


KEY1 KEY2 KEY3 COL VAL

AAA BBB CCC A1 1
AAA BBB CCC A2 2
AAA BBB CCC A3 3
...
...
...
AAA BBB CCC A1000 1000

I need to pivot this data on the composite key which is defined on the only the first 3 columns.

The tricky part begins now. The data from the VAL column needs to be mapped to a destination table where the name of the column is found in the COL column.
The desired result would thus be:


KEY1 KEY2 KEY3 A1 A2 A3 ....... A1000

AAA BBB CCC 1 2 3 ...... 1000

Using the Pivot Enterprise stage I can get something like


KEY1 KEY2 KEY3 COL VAL COL_1 VAL_1 COL_2 VAL_2 ....... COL_1000 VAL_1000

AAA BBB CCC A1 1 A2 2 A3 3 ...... A1000 1000

However I need to map each value to a particular column, whose name is specified in the source column.


Any ideas folks?

Thanks

[ShaneMuir]

I assume that the difficulty is that not all the values for all the target columns will be present for each record? Ie you might only get 10 columns of the 1000 in your source data?

This post might help viewtopic.php?t=154172

You would need to tweak it a little. Get your list of target column names from your DBs system table to create the target list of column names, then use your incoming column name value to get the column position. Then you should be able to follow the post above to output a required string of values to update.

You can then use a column import stage to separate the string back into separate target columns for loading to your DB.