Five 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.
  • Data Integration
2 ACCEPTED SOLUTIONS

Accepted Solutions
Six Stars

Re: Transpose Column value into Rows

Perhaps this can help you?

 

 

the resultthe result

 

 

Incoming dataIncoming data

 

tExtractDelimitedFields ConfigurationtExtractDelimitedFields Configuration

 

tDenormalize ConfigurationtDenormalize Configuration

 

Schemas, NOTE the key!Schemas, 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?

 

 

the resultthe result

 

 

Incoming dataIncoming data

 

tExtractDelimitedFields ConfigurationtExtractDelimitedFields Configuration

 

tDenormalize ConfigurationtDenormalize Configuration

 

Schemas, NOTE the key!Schemas, NOTE the key!

 

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

Re: Transpose Column value into Rows

Perfectly working! Thanks!!
Five 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.