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
changing a column to row
Moderators: chulett, rschirm, roy
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![Question :?:](./images/smilies/icon_question.gif)
What is the key for pivoting?
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
![Question :?:](./images/smilies/icon_question.gif)
What is the key for pivoting?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
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
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
Feed this to a hashed file with two columns. First will be the key hardcoded to say 'x'. Second column will be vPivot.
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
Last edited by DSguru2B on Thu Jan 25, 2007 2:06 pm, edited 1 time in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.