Six Stars

Transpose Column value into Rows

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.
2 ACCEPTED SOLUTIONS

Accepted Solutions
Six Stars

Re: Transpose Column value into Rows

Perhaps this can help you?

 

 

Screenshot from 2017-08-17 14-35-07.pngthe result

 

 

Screenshot from 2017-08-17 14-35-30.pngIncoming data

 

Screenshot from 2017-08-17 14-35-39.pngtExtractDelimitedFields Configuration

 

Screenshot from 2017-08-17 14-35-58.pngtDenormalize Configuration

 

Screenshot from 2017-08-17 14-36-13.pngSchemas, NOTE the key!

 

-----------------------------------------------------------------------------------------------------------
When you like my answer, please accept it as solution and send some kudos
Ten Stars

Re: Transpose Column value into Rows

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.

4 REPLIES
Six Stars

Re: Transpose Column value into Rows

Perhaps this can help you?

 

 

Screenshot from 2017-08-17 14-35-07.pngthe result

 

 

Screenshot from 2017-08-17 14-35-30.pngIncoming data

 

Screenshot from 2017-08-17 14-35-39.pngtExtractDelimitedFields Configuration

 

Screenshot from 2017-08-17 14-35-58.pngtDenormalize Configuration

 

Screenshot from 2017-08-17 14-36-13.pngSchemas, NOTE the key!

 

-----------------------------------------------------------------------------------------------------------
When you like my answer, please accept it as solution and send some kudos
Six Stars

Re: Transpose Column value into Rows

Perfectly working! Thanks!!
Six Stars

Re: Transpose Column value into Rows

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

Re: Transpose Column value into Rows

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.