Hi,
my database table structure looks like
Year Month Cust_no Prod_no Dol_day1 Dol_day2 Dol_day3 Dol_day4......Dol_day31
2004 10 100 200 100.00 200.00 300.00 400.00.... 3100.00
2004 10 100 201 111.00 222.00 333.00 444.00 ....3400.00
I need to covert that table into
Year Month Cust_no Prod_no Day_Num Dollars
2004 10 100 200 1 100.00
2004 10 100 200 2 200.00
2004 10 100 200 3 300.00
2004 10 100 200 4 400.00
.
.
.
.
2004 10 100 200 31 3100.00
2004 10 100 201 1 111.00
2004 10 100 201 2 222.00
2004 10 100 201 3 333.00
2004 10 100 201 4 444.00
.
.
.
.
2004 10 100 201 31 3400.00
Is there any clue which stage i can use in Datastage?
Thanks
Converting colums into rows
Moderators: chulett, rschirm, roy
The easiest way that I know to do this is with stage variables in a transformation following the pivot. The rows generated will be in the same order that the columns were so Dol_day1 will be the first row followed by day2, day3 and so on. So create a stage variable that loops from 1 to 31 and assign this to the record.
Code: Select all
svDayNum = If svDayNum = 31 then 1 else svDayNum + 1
Keith