Converting rows to columns
Moderators: chulett, rschirm, roy
Converting rows to columns
I am a new user of Datastage. I am trying to convert one sequential file to another, where the rows in the input will be columns in the output.
Input: (Key-ID+Qtr)
ID;Qtr;Qty
1112;Q1;10
1112;Q2;87
1112;Q3;874
1112;Q4;91
1134;Q1;40
1134;Q3;43
Output: (Key-ID)
ID;Q1QTy;Q2Qty;Q3Qty;Q4Qty
1112;10;87;874;91
1134;40;;43;;
Can some one help me to do this, please?
Input: (Key-ID+Qtr)
ID;Qtr;Qty
1112;Q1;10
1112;Q2;87
1112;Q3;874
1112;Q4;91
1134;Q1;40
1134;Q3;43
Output: (Key-ID)
ID;Q1QTy;Q2Qty;Q3Qty;Q4Qty
1112;10;87;874;91
1134;40;;43;;
Can some one help me to do this, please?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard, arn73. :D
What you are seeking to do is called a "horizontal pivot", so the suggestion made by logic won't help.
The good news is that what you're trying to do is exactly what the Pivot stage does. Read about it in the Pivot.pdf manual (this is in the Docs folder where your DataStage client is installed). If you have any remaining questions, please feel welcome to post back here.
You might also find this post useful.
What you are seeking to do is called a "horizontal pivot", so the suggestion made by logic won't help.
The good news is that what you're trying to do is exactly what the Pivot stage does. Read about it in the Pivot.pdf manual (this is in the Docs folder where your DataStage client is installed). If you have any remaining questions, please feel welcome to post back here.
You might also find this post useful.
Last edited by ray.wurlod on Thu Oct 27, 2005 5:11 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Arn73, Ray,
I am sorry. Actually I did not read the whole post. I read the following part and actually wanted to point at it. My Bad.
I am sorry. Actually I did not read the whole post. I read the following part and actually wanted to point at it.
Code: Select all
You're doing what is called a pivot. You can checkout the Pivot stage
arn73,arn73 wrote:It appear to be slightly different. I have the output file where the columns are the second part of the input file key. Also, the number of columns in the output is fixed.
The placement of the column over which you want to pivot does not matter. The advice given in the post pointed to are valid for you. You could search the forum on how to use stage variables for this.
ok.. I did the search and HERE you will find the answer. Its a 2 year old post
![Wink :wink:](./images/smilies/icon_wink.gif)
I do have a solution to convert rows into multiple columns. However, I do it in the database, instead of in the DataStage. I know very well that this might be a trivia here.
For instance, in MS SQL Server, using T-SQL, I wrote a User-Defined function, and I use that function in the SELECT statement, as we would use any normal FieldName. This function transposes the rows to multiple columns, using cursors (not an efficient way, but it works 100 percent).
Again, this may be outside of DataStage, but it works perfectly. And since this is a tested and working solution, I did not waste much time over this in DataStage. Moreover, the Pivot stage only does a horizontal pivot, and NOT a vertical pivot, which most of us are looking for here.
Hari
For instance, in MS SQL Server, using T-SQL, I wrote a User-Defined function, and I use that function in the SELECT statement, as we would use any normal FieldName. This function transposes the rows to multiple columns, using cursors (not an efficient way, but it works 100 percent).
Again, this may be outside of DataStage, but it works perfectly. And since this is a tested and working solution, I did not waste much time over this in DataStage. Moreover, the Pivot stage only does a horizontal pivot, and NOT a vertical pivot, which most of us are looking for here.
Hari
A SQL query, using CASE or DECODE statements to logically move information to the appropriate column, using GROUP BY yourkey style logic with MAX() derivations on all CASE derived columns will give you a vertical pivot.
EX:
This approach is not very scalable for a lot of columns, but it's pretty handy. You can also pivot and summary in one swell foop by changing MAX to SUM.
EX:
Code: Select all
select key, max(val_1), max(val_2), max(val_3) from (
select key,
case rowtype=1 then value else null end "VAL_1",
case rowtype=2 then value else null end "VAL_2",
case rowtype=3 then value else null end "VAL_3"
from yourtable
)
group by key
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle