concatenate the rows

[dodda]

Hello

I have the below requirement.

col1(key) col2 col3 col4
1 A B C
1 D E F
1 G H I
1 J K L
2 M N O
2 P Q R
2 S T Q
2 U V W

based on the key column i need to concatenate col3 values. the output should be as below
col1 col2
1 BEHK
2 NQTV

any ideas would be appreciated.

Thanks

[chulett]

That would be a horizontal pivot of rows to columns, search for that phrase here to find the many (many) conversations we've already had on the topic.

[dodda]

That would be a horizontal pivot of rows to columns, search for that phrase here to find the many (many) conversations we've already had on the topic.

Hello Chulett,

Thanks for the reply.i looked at the horizontal pivot pdf but my requirement seems to be different. My aim is based on the key (col1) from the source i need to concatenate col3 values from the source which are varchar datatype . so i am expecting the output as

1 BEHK
2 NQTV

Thanks

[ray.wurlod]

Pivot first, concatenate later. A good rule to remember in parallel job design is "one stage, one task". That's why there are so many more stage types in the parallel job Palette than in the server or mainframe job Palettes.

[chulett]

Your requirement isn't different, it is still a horizontal pivot of columns to rows around a key column or columns. It's just that you get to pivot only one of the data columns and pitch the rest.

[jaysheel]

Use 3 stage variables.

NewKey = Key Column
ConcatData = If NewKey <> NextKey ColumnC Else ConcatData:ColumnC
NextKey = NewKey


Use ConcatData stage variables for the new column derivation.
Hope this helps.