name-value pairs
Posted: Tue Dec 04, 2007 3:11 pm
Hi,
I have to perform a lookup by using the column name as the source of the lookup (not the value of the column). Hence, I need to create a name value pair so that I can use the column name. Below is a brief example of what I am trying to accomplish
I have the following format definition and source data
Key, Col1, Col2, Col3
123, ab, cd, ef
I am trying to create the following target dataset
Key, Name, Value
123, Col1, ab
123, Col2, cd
123, Col3, ef
I currently have a way to create the target dataset by concatenating the column names to the end of each input row and then using the pivot to generate the output dataset. The following is what I mean when I say concatenate the column names...
Key, Col1, Col2, Col3, Col4, Col5, Col6
123, ab, cd, ef, Col1, Col2, Col3
In reality I have about 100+ columns on the input dataset and concatenating the 100+ column names as extra columns to each input row is not desired. Is there a better way of doing this? Has anybody tried this before? Your help is much appreciated.
Thanks.
Suresh
I have to perform a lookup by using the column name as the source of the lookup (not the value of the column). Hence, I need to create a name value pair so that I can use the column name. Below is a brief example of what I am trying to accomplish
I have the following format definition and source data
Key, Col1, Col2, Col3
123, ab, cd, ef
I am trying to create the following target dataset
Key, Name, Value
123, Col1, ab
123, Col2, cd
123, Col3, ef
I currently have a way to create the target dataset by concatenating the column names to the end of each input row and then using the pivot to generate the output dataset. The following is what I mean when I say concatenate the column names...
Key, Col1, Col2, Col3, Col4, Col5, Col6
123, ab, cd, ef, Col1, Col2, Col3
In reality I have about 100+ columns on the input dataset and concatenating the 100+ column names as extra columns to each input row is not desired. Is there a better way of doing this? Has anybody tried this before? Your help is much appreciated.
Thanks.
Suresh