Transpose Columns into rows

karthi_gana · Posted: Fri Jul 30, 2010 12:36 am

All,

I have the file like below

Code:

ven_symbol 3 5 6 8 9 10 12 13
ABC 20.5 19.5 4.3 3.2 2.9 1.8 12.5 7.5
DEF 40.5 12.5 4.3 5.2 6.9 7.8 72.5 8.5

I would like to transpose the file as below

Code:

ven_symbol code value
ABC 3 20.5
ABC 5 19.5
ABC 6 4.3
ABC 8 3.2
ABC 9 2.9
ABC 10 1.8
ABC 12 12.5
ABC 13 7.5
DEF 3 40.5
DEF 5 12.5
DEF 6 4.3
DEF 8 5.2
DEF 9 6.9
DEF 10 7.8
DEF 12 72.5
DEF 13 8.5

karthi_gana · Posted: Fri Jul 30, 2010 1:13 am

I did some search here and found that i have to use 'PIVOT' stage to accomplish this requirement.

But i splitted the output like below

ven_symbol value
ABC 20.5
ABC 19.5
..
..
..

But how should i include the column name like below?

ven_symbol code value
ABC 3 20.5
ABC 5 19.5
..
..
..

ray.wurlod · Posted: Fri Jul 30, 2010 1:28 am

You forgot to include the code column in your Pivot stage as a pivoting column.

karthi_gana · Posted: Fri Jul 30, 2010 3:32 am

ray.wurlod wrote:

You forgot to include the code column in your Pivot stage as a pivoting column.

???

I have given the columns as below in the output section of PIVOT.

Code:

columnname derivation
ven_symbol
value e3,e5,e6,e8,e9,e10,e12,e13

if i include 'code' in the output, what should i use in the derivation?

karthi_gana · Posted: Fri Jul 30, 2010 3:59 am

Code:

Payments Example
CUSTID LNAME JAN_Sales FEB_Sales MARCH_Sales JUN_Pay DEC_Pay
100 Smith $1,234.00 $1,456.00 $1,578.00 $6,298.00 $7,050.00
101 Yamada $1,245.00 $1,765.00 $1,934.00 $7,290.00 $7,975.00

Output Data in Target Rows After Pivot

Code:

CUSTID LNAME Sales Payments
100 Smith $1,234.00 $6,298.00
100 Smith $1,456.00 $7,050.00
100 Smith $1,578.00 null
101 Yamada $1,245.00 $7,290.00
101 Yamada $1,765.00 $7,975.00
101 Yamada $1,934.00 null

This example has been taken from 'pivot.pdf'.

My requirement is , i need

JAN_Sales FEB_Sales MARCH_Sales

in the output like below

Code:

CUSTID LNAME Month Sales Payments
100 Smith JAN_Sales $1,234.00 $6,298.00
100 Smith FEB_Sales $1,456.00 $7,050.00
100 Smith MARCH_Sales $1,578.00 null
101 Yamada JAN_Sales $1,245.00 $7,290.00
101 Yamada FEB_Sales $1,765.00 $7,975.00
101 Yamada MARCH_Sales $1,934.00 null

Sainath.Srinivasan · Posted: Fri Jul 30, 2010 4:04 am

Will the input columns be static ? If yes, then pass the rows as separate links from a transformer and collect using link collector. If it is variable, store the first row in a stage variabl ...

What's this?

karthi_gana · Posted: Fri Jul 30, 2010 4:11 am

Sainath.Srinivasan wrote:

Will the input columns be static ?

yes.

But i am not getting your point. can you please explain it?

ray.wurlod · Posted: Fri Jul 30, 2010 5:46 am

Essentially you have to include the numeric column headings' values as the sources for the pivot column code - given that they are constant, you can use a Transformer stage to generate the values.

karthi_gana · Posted: Fri Jul 30, 2010 6:03 am

ray.wurlod wrote:

given that they are constant, you can use a Transformer stage to generate the values.

how should i generate the values? I don't know the way to do it in datastage. i mean the methods i follow. can you give me the sample job design here?

chulett · Posted: Fri Jul 30, 2010 6:05 am

You add them yourself, manually.

karthi_gana · Posted: Fri Jul 30, 2010 6:07 am

chulett wrote:

You add them yourself, manually.

can you give me the sample job design here?

chulett · Posted: Fri Jul 30, 2010 6:21 am

Add four new columns to the transformer, manually populate them with your heading names ("JAN_Sales", etc) and pivot them along with their actual, matched data.

karthi_gana · Posted: Fri Jul 30, 2010 7:31 am

chulett wrote:

Add four new columns to the transformer, manually populate them with your heading names ("JAN_Sales", etc) and pivot them along with their actual, matched data.

yes. it is working perfectly Smile

Thanks Craig and Ray Smile

How about the performance of this job? If i receive around 500,000 records every day, will it leads to performance issue?

I have referred some old projects and they used 'ROUTINE' to do 'Row to Column' transpose. Initially, I have been asked to modify the existing routine to do 'Column to Row' transpose. But as i am not that much strong in writing routines,I looked for some work around to do this. I did some search here and got the solution with the help of my datastage mentors(Ray,Craig,Sainath,Arnd etc.,).

But i would like to know which one will be good when we think about performance?

1) PIVOT method
2) ROUTINE

arunpramanik · Posted: Fri Jul 30, 2010 7:32 am

Here is another way to do -

Consider your file contains Column Header.

read the file
seperate into two streams
Stream1 will have column header (@inrownum =1)
Stream2 will have data (@inrownum >1)

for Stream1 -
pivot the cloumn header
custid key
lname = lname
mth = jan_sales, ...........,dec_pay
assign a new col rwnm =@outrownum
store it to hash file with rwnm (as key)

hash file will store the following
rwnm , mth
1, jan_sales
....
5, dec_pay

for Stream2 -
do the pivot similarly
assign a new column rwnm
generate rwnm using stage variables
store the data in seq file

seq file will look like -
custid, lname, rwnm, amt
100,"abc", 1, 100
....
100,"abc",5, 500
101,"def",1, 1000
....
101."def",5,5000

now join seqfile and hash file with seqfile.rwnum =hashfile.rwnum
and get the desired output