Pivot rows
Moderators: chulett, rschirm, roy
Pivot rows
Hi All,
I need to implement one of the logic in my job. In the source table having 6 columns in one row and that row I need to be pivoted like I mentioned below.
Source table:
col1,col2,col3,col4,col5,col6
1,2,3,4,5,6
Output:
1,2,3
2,3,4
4,5,6
5,6,null
6,null,null
Any help is really appreciated.
I need to implement one of the logic in my job. In the source table having 6 columns in one row and that row I need to be pivoted like I mentioned below.
Source table:
col1,col2,col3,col4,col5,col6
1,2,3,4,5,6
Output:
1,2,3
2,3,4
4,5,6
5,6,null
6,null,null
Any help is really appreciated.
prabakaran.v
I have 6 columns in the source table with data as follows.
Source Data:
COL1,COL2,COL3,COL4,COL5,COL6
A,B,C,D,E,F
The output should be
A,B,C
B,C,D
C,D,E
D,E,F
E,F,NULL
F,NULL,NULL
The output will have only 3 columns.
The data should be like this.
First row: first column, second column, third column
Second row: second column, thirdcolumn, fouth column.
..
.
Sixthrow: sixth column, null, null
So first row will start with first column, second row start with second column, similarly sixth row starts with sixth column. When there is no data for the sixth row, there should be null.
Hope I have explained the issue clearly this time. Please let me know how to fix this issue
Source Data:
COL1,COL2,COL3,COL4,COL5,COL6
A,B,C,D,E,F
The output should be
A,B,C
B,C,D
C,D,E
D,E,F
E,F,NULL
F,NULL,NULL
The output will have only 3 columns.
The data should be like this.
First row: first column, second column, third column
Second row: second column, thirdcolumn, fouth column.
..
.
Sixthrow: sixth column, null, null
So first row will start with first column, second row start with second column, similarly sixth row starts with sixth column. When there is no data for the sixth row, there should be null.
Hope I have explained the issue clearly this time. Please let me know how to fix this issue
prabakaran.v
-
- Participant
- Posts: 34
- Joined: Tue Feb 26, 2013 9:36 am
- Location: Chicago
After pivotting the data, you can add a transformer and check the outcome of Column6. If Column6='' Then 'null' else column6.
I am not sure if this is what you are expecting. Pivot Enterprise Edition in 8.5 has checkboxes to select the Group by columns and columns that you may want to Pivot. If you are working on version lower than 8.5, you may need to define the column that may need to pivot in the derivation part of the pivot stage.
I am not sure if this is what you are expecting. Pivot Enterprise Edition in 8.5 has checkboxes to select the Group by columns and columns that you may want to Pivot. If you are working on version lower than 8.5, you may need to define the column that may need to pivot in the derivation part of the pivot stage.
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Hi,
If you are using datastage 8.5 or higher u can pivot data either using pivot enterprice stage or using looping acitivity in transformer stage.
In case you are in lower version you can split the incoming records in transformer using field function and take 6 output links out from transformer and funnel them.
Once pivot is done put another transformer and sort in descending order and create few stage veriable to save current and previous and pre to previous recors and then output to three colums.
Currently I don't have datastage,just try with this,if you face any issue let me know.
If you are using datastage 8.5 or higher u can pivot data either using pivot enterprice stage or using looping acitivity in transformer stage.
In case you are in lower version you can split the incoming records in transformer using field function and take 6 output links out from transformer and funnel them.
Once pivot is done put another transformer and sort in descending order and create few stage veriable to save current and previous and pre to previous recors and then output to three colums.
Currently I don't have datastage,just try with this,if you face any issue let me know.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
The post you quoted from rameshrr3 (which I removed from your post, please learn to just Reply) doesn't have anything to do with transformer looping. Not only does their solution not require any looping, it would be dead easy to implement.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Chulett,
Why I asked for looping concept, I have to implement the same logic in another job and source table having 60 columns. It is really difficult for me to implement having 60 rows in the same job.
That's why I asked how to implement the same logic using loop variables.
Need output is:
1,2,3
2,3,4
3,4,5
4,5,6
5,6,7
......
......
59,60,null
60,null,null
Why I asked for looping concept, I have to implement the same logic in another job and source table having 60 columns. It is really difficult for me to implement having 60 rows in the same job.
That's why I asked how to implement the same logic using loop variables.
Need output is:
1,2,3
2,3,4
3,4,5
4,5,6
5,6,7
......
......
59,60,null
60,null,null
prabakaran.v
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: