How can you pivot data (cross tab and transpose)
cross-tab and transpose data
1-2-3-4
a-b-c-d
1 a
2 b
3 c
5 d
Considering sources as below.
Source 1:
Source 2:
And here col1, col2, col3, col4 in each source needs to be converted to rows.
Use the source 1 as source instance in the data flow four times and link them to a union transformation. In each source instance add an attribute to indicate the unique number to the column.
Source instances
Union transformation
Output of union transform
Similarly do this for source2 as well.
Output from the union transformations can be connected to the joiner transform and join based col_nm. The other column from the union should be added as attributes in the joiner.
Attributes in joiner
Output of joiner
If the source is external then it is recommended to stage it first in the processing platform and use the staged data to do above.