Splitting a single record into multiple records

panchusrao2656 · Post by **panchusrao2656** » Fri Oct 20, 2006 9:46 am

I have a record with five fieldss like
Column Names :A B C D E
Column Values :1 2 3 4 5

i want to convert it to 3 records with four fields as following:

Column Names : A B COL_NAME COL_VAL
Column Values : 1 2 C 3
Column Values : 1 2 D 4
Column Values : 1 2 E 5

I have 85(2+83) fields that needs to be converted to 83 records with 4 (2+2) fields.

I donot know how to get the third field using PIVOT, please share your ideas.

Thank You

samsuf2002 · Post by **samsuf2002** » Fri Oct 20, 2006 9:49 am

do a search on "virtual pivot" may be this can help u

ray.wurlod · Post by **ray.wurlod** » Fri Oct 20, 2006 2:39 pm

VERTICAL pivot would be an even better search. The Pivot stage will accomplish this task for you.

kumar_s · Post by **kumar_s** » Fri Oct 20, 2006 6:25 pm

Hard code the column name next to each column using a transformer.
In PIVOT stage, mark A and B as key, and pass all hard coded column name into COL_NAME and the column value to COL_VAL.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 20, 2006 8:38 pm

Generate a new column containing the column names (destined for COL_NAME) as a constant using a Cycle Generator with just one value.

kumar_s · Post by **kumar_s** » Sat Oct 21, 2006 7:56 am

Provided the actual value is A,B,C,D...

ray.wurlod · Post by **ray.wurlod** » Sat Oct 21, 2006 4:48 pm

Doesn't matter what the actual values are. I'm sure A,B,C,D was just an example. You can hard code any string value such as

Code: Select all

"ItemNo,ItemClass,PkgType,PkgQty"

Unfortunately in this case it will require 83 comma-delimited fields. On the up side, it will only need to be constructed once. Copy/paste it to your Description field and to your target-from-source mapping documentation and you'll never need to type it again, unless the column names change in future.