Converting colums into rows

[ksmurthys]

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

[KeithM]

You can use the pivot stage to convert columns to rows.

[ksmurthys]

Keith,

whatever u said is true when we convert columns into rows.But i need to generate one more column values for day_num(i.e 1,2,3,...31).Is there anyway i can generate numbers based on those columns Dol_day1 is 1,Dol_day2 is 2 ... Dol_day31 is 31.

Thanks.

[KeithM]

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