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
Pivoting scenario
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
-
- Premium Member
- Posts: 48
- Joined: Tue Nov 25, 2008 11:10 pm
- Location: Des Moines,IA
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=
viewtopic.php?t=97681&highlight=
viewtopic.php?t=118685&highlight=
"It's easier to go down a hill than up it but the view is much better at the top"
-Bennet,Arnold
-Bennet,Arnold
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).
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).
Nirdesh Kumar
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
But in my scenario, i want to convert many columns into one columnnirdesh2 wrote: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).
![Sad :(](./images/smilies/icon_sad.gif)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
vertical pivot
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.
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.