Concatenate Rows in Single Column

mohdtausifsh · Post by **mohdtausifsh** » Tue Mar 15, 2011 6:26 am

HI
I have Requirement like this

Input
PID JOBNAME
1 A
1 B
1 C
.
.
. X

My output should be

PID JOBNAME
1 A,B,C.,.,.X

Could you please help me in acheiving in datastage

Thanks in Advance
Mohammed Tausif

chulett · Post by **chulett** » Tue Mar 15, 2011 7:06 am

That would be a 'vertical pivot' (aka rows to columns) and an exact search here will reveal the options you have.

mohdtausifsh · Post by **mohdtausifsh** » Tue Mar 15, 2011 7:17 am

If it is verticle pivot for rows in to column we can acheive it in transformer,but here the rows should be concatenated in to single column.

chulett · Post by **chulett** » Tue Mar 15, 2011 7:25 am

Still a vertical pivot and to me the easiest way to solve the 'concatenation' part in a Server job is to use a Hashed File. Read and write to it in the same transformer, create a new record for each key on a lookup miss and append values on a lookup hit. Make sure you do not cache the lookup.

A search here would have revealed this.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 15, 2011 4:05 pm

Or just do the appending in a Transformer stage. Follow the Transformer stage with an Aggregator stage that captures the last row of each group.

chulett · Post by **chulett** » Tue Mar 15, 2011 6:34 pm

Or that... another technique the search would have turned up.

One approach requires sorted data, the other does not.