Transpose Column value into Rows

[rajesh90]

Team, I have a input like below, Input Data : ID|Name 1|Ram 1|Raj 1|Sam 2|Sham 2|Subbu 3|Ramar 3|Umar 4|Peter Output Required : 1RamRajSam 2ShamSubbu 3RamarUmar 4Peter Please note that the "ID" column count will vary. Please let me know the best suitable approach for the same.

[Aukema · Accepted solution]

Perhaps this can help you?

the result

Incoming data

tExtractDelimitedFields Configuration

tDenormalize Configuration

Schemas, NOTE the key!

[rajesh90]

Perfectly working! Thanks!!

[rajesh90]

Quick check what is the difference between the tdenormalize n tdenormalizesortedrow?

[cterenzi]

tDenormalize will build one output row for each key value or key group in the input, regardless of the order of the rows.

tDenormalizeSortedRow builds a new output group each time the key value or key group in the input changes.

It's easier to understand with a visual.

INPUT:

1|Seg_1
2|Seg_2
2|Seg_3
2|Seg_1
1|Seg_2
3|Seg_3
3|Seg_1
3|Seg_2
1|Seg_3

tDenormalize

1|Seg_1;Seg_2;Seg_3
2|Seg_2;Seg_3;Seg_1
3|Seg_3;Seg_1;Seg_2

tDenormalizeSortedRow

1|Seg_1
2|Seg_2;Seg_3;Seg_1
1|Seg_2
3|Seg_3;Seg_1;Seg_2
1|Seg_3

The sorted version can be faster and less memory intensive under the right circumstances.  There may also be situations where you need to rely on the order of the input rows to group records.