changing a column to row

vsi · Post by **vsi** » Thu Jan 25, 2007 1:10 pm

Hi folks,

i am working to change a column to a row.

for ex:-

soruce consisits of a column name Country

country :-

usa
india
china
japan

the result should be

usa, india, china, japan

Please help to resolve this.

Thanks in advance

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 1:17 pm

What does your input data look like. Give us sample date. Complete meta data also.
Just looking at what you gave us doesnt help us. On what basis is your code going to know that it has to stop pivoting and start afresh

What is the key for pivoting?

vsi · Post by **vsi** » Thu Jan 25, 2007 1:38 pm

Thanks for u r response dsguru,

Inputdata:-
B,Employee01,01
C,Child01_01,02
C,Child01_02,03
B,Employee02,04
C,Child02_01,05
B,Employee03,06
B,Employee04,07
C,Child04_1 ,08
C,Child04_2 ,09
C,Child04_3 ,10
B,Employee05,11

Expected result should be in one record like

B,Employee01,01,C,Child01_01,02,C,Child01_02,03,B,Employee02,04,C,Child02_01,05,B,Employee03,06,B,Employee04,07,C,Child04_1 ,08,C,Child04_1 ,08,C,Child04_2 ,09,C,Child04_3 ,10,B,Employee05,11

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 1:49 pm

So everything in a straight line. Its going to be a very long line if you have huge number of records.
You can use stage variables to attach, concatenate the columns and load it to a hashed file keyed on a dummy static key. Once your done. your hashed file will have a single line with everything concatenated together. Read the file as a single column. Set the quote character and delimiter to 000.
Say your stage variable is called vPivot. Initialize it to "". Its derivation will be

Code: Select all

if @INROWNUM = 1 then DSLink2.Col else vPivot:",":DSLink2.key

Feed this to a hashed file with two columns. First will be the key hardcoded to say 'x'. Second column will be vPivot.

pavankvk · Post by **pavankvk** » Thu Jan 25, 2007 2:05 pm

yes..it can be done thru stage variables. u can also call a awk script if ur input volume is low.

gvstrao · Post by **gvstrao** » Sun Jan 28, 2007 5:10 pm

Hi All,

I am working on PX , that's why I am using flat files. I am getting the same number of records in the target file also.

The given code did't concatinate the records.

if @INROWNUM = 1 then DSLink3.Field else vpivot : "," : DSLink3.Field

could anyone help me

Thanks in Aavance

gvstr

DSguru2B · Post by **DSguru2B** » Sun Jan 28, 2007 5:48 pm

Welcome Aboard,
Start a new post. Specify a link to this post in your post for reference. What you are doing is hijacking a post and is not apprciated.