Column1 Column2 Column3 1 2 3
Row1 a b c 1000 2000 3000
The values present in Column4('1','2','3') become column names of the transposed data. I believe I might need to use the pivot stage but having difficulty in converting column values to column names. Any help is highly appreciated.
Simply hard code the column names "c1", "c2", "c3". DataStage will not permit numeric column names. You can change them downstream if necessary (and in row #1 only).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Column1 Column2 Column3 Column4 Column5
Row1 a b c c1 1000
Row2 a b c c2 2000
Row3 a b c c3 3000
Row4 a b c c4 4000
Row5 d e f c1 5000
Row6 d e f c3 6000
Row7 d e f c4 7000
Which version of DataStage you are using? If it is >= 8.5, then you can use vertical pivot method in pivot stage. In this case, you need to assume that you will always have 4 rows coverted to 4 columns. Any concern on null fields for groups having less than 4 rows?
Or
Use stage variables in transfomer stage (you can search the forum for "vertical pivot"). Sort & select the required record.
Or
Do it is BASIC language or C++.
Kandy
_________________
Try and Try again…You will succeed atlast!!
The logic to apply nulls for those having less than 4 records is not tacit.
If the maximum number of records per group is fixed, then we can use that and decide the maximum no of columns to be created .
I was under the assumption that the LastRowInGroup() funtion in a Transformer stage will work on more than one input column concatenated together. But, looks like my assumption is wrong and the LastRowInGroup() funtion takes only one input column as a parameter. Is that so?