Pivot Enterprise Stage - transforming columns to single line
Posted: Tue May 19, 2015 4:13 am
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
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