Transpose/pivot logic needed

[pdntsap]

Hello,

We have a requirement to transpose data as follows:

Code: Select all

           Column1  Column2  Column3  Column4  Column5
Row1       a         b         c         1     1000
Row2       a         b         c         2     2000
Row3       a         b         c         3     3000

We need to transpose the input and produce:

Code: Select all

      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.

Thanks.

[ray.wurlod]

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).

[pdntsap]

Thanks Ray.

I had one more questions. According to the requirements, the number of rows can be between 1 and 4. For example:

Code: Select all

        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

The above data should be transposed as:

Code: Select all

       Column1  Column2  Column3      c1       c2       c3       c4
Row1     a        b         c        1000     2000     3000      4000
Row2     d        e         f        5000     NULL     6000      7000         

Any suggestions on implementing the above is also greatly appreciated.

Thanks.

[qt_ky]

It looks similar to the parallel developer product manual examples of Transformer loop variables in 8.5 and higher.

[kandyshandy]

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++.

[pandeesh]

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 .

[pdntsap]

Which version of DataStage you are using?

My version is 8.7.

Any concern on null fields for groups having less than 4 rows?

The transposed data must have four columns for each group with the value being NULL for any missing rows.

Thanks for the suggestions and I will try them out.

[pdntsap]

I looked at the solution provided in the following thread:

viewtopic.php?t=145258&highlight=vertical+pivot

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?

Thanks.

[qt_ky]

It takes one column and the column must first be sorted. You could concat many columns into one column, sort on it, then use the function on it.