Pivoting scenario

[prasson_ibm]

Hi ,
I have one scenario in wihch i have to pivot rows into column.And i have no idea to use a PIVOT stage.
I have like this:-

i/p:-
col a b c d
val 22-44 33-32 99-88 55-11
and i want o/p like this:-
col1 col2
22 44
33 32
99 88
55 11

please help me in resolving this scenario..

Regards

[datskosaraju]

It's a vertical pivot and has been discussed here before. Hope the below links helps!!



viewtopic.php?t=97681&highlight=
viewtopic.php?t=118685&highlight=

[nirdesh2]

Below mentioned is the example how Pivot stage works...

The input link Columns page contains three input columns with sales data:
JAN_Sales, FEB_Sales, and MARCH_Sales. The columns are as follows:
The data for the source rows for the input columns looks like this:
Input Source Rows
CUSTID LNAME JAN_Sales FEB_Sales MARCH_Sales
100 Smith $1,234.00 $1,456.00 $1,578.00
101 Yamada $1,245.00 $1,765.00 $1,934.00




The three output rows of sales data that result from each input row in this example
are as follows:
Output Target Rows
CUSTID Last_Name Sales
100 Smith $1,234.00
100 Smith $1,456.00
100 Smith $1,578.00
101 Yamada $1,245.00
101 Yamada $1,765.00
101 Yamada $1.934.00


Derivation for output column Sales will be (JAN_Sales,FEB_Sales,MARCh_Sales).

[prasson_ibm]

Below mentioned is the example how Pivot stage works...

The input link Columns page contains three input columns with sales data:
JAN_Sales, FEB_Sales, and MARCH_Sales. The columns are as follows:
The data for the source rows for the input columns looks like this:
Input Source Rows
CUSTID LNAME JAN_Sales FEB_Sales MARCH_Sales
100 Smith $1,234.00 $1,456.00 $1,578.00
101 Yamada $1,245.00 $1,765.00 $1,934.00




The three output rows of sales data that result from each input row in this example
are as follows:
Output Target Rows
CUSTID Last_Name Sales
100 Smith $1,234.00
100 Smith $1,456.00
100 Smith $1,578.00
101 Yamada $1,245.00
101 Yamada $1,765.00
101 Yamada $1.934.00


Derivation for output column Sales will be (JAN_Sales,FEB_Sales,MARCh_Sales).

But in my scenario, i want to convert many columns into one column

[ray.wurlod]

Nirdesh has taken the trouble to type out the horizontal pivot example from the manual, whereas what you want is a vertical pivot. As noted, you can find solutions via Search of DSXchange.

[rcanaran]

Not sure where to post this, but another solution to the vertical pivot I found was this :

Use an XML Output stage, specifying your key columns and use the "aggregate rows" option in the output.
You can concatenate mutliple columns into a singel row.

No need for the transformer logic to build the record. No need for an extra recrod to flush the last data row or a dedup to filter out the intermediate rows.

But, depending on the task at hand, you may need to strip out the xml tags afterward (stage variables or transformer procesing, or external filter to a SED) or use modify stage/2nd job wiht different metadata to read the data as a new set of columns.