Pivot rows

[praburaj]

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.

[chulett]

You'd have to explain what your pivoting rules are in plain English. Right now, the output example is confusing. Did you leave out a row that should show "3,4,5"?

[praburaj]

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

[chulett]

So the short answer would have been yes.

What 8.x version are you on - at least 8.5?

[sudha03_vpr]

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.

[prasson_ibm]

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.

[rameshrr3]

Can you not use a transformer & funnel stage ?
COl1, Col2 Col3 for input link1
Col2 , Col3, COl4 for input link 2
Col3,Col4,Col5 for input link 3
Col4,COl5,COl6 for input link 4
Col5,Col6,NUl for input link 5
Col6,NUL,NUL for input link 6

Combine all input links using round robin in funnel.

[praburaj]

Really thanks for all your valuable input. I am using version 8.5 and loop logic is really new for me. Can you help me how can I implement this logic using loop variable.

[chulett]

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.

[praburaj]

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

[ray.wurlod]

You could use a Transformer stage with 60 output links brought back together in a Funnel stage.

[chulett]

Exactly. Not sure what about that is difficult... tedious perhaps but not difficult.

[ShaneMuir]

But if you used Loop functionality, combined with some RCP, and schema files you could write the one job to do both circumstances (or any number of input columns).