Converting rows to columns

[arn73]

I am a new user of Datastage. I am trying to convert one sequential file to another, where the rows in the input will be columns in the output.

Input: (Key-ID+Qtr)

ID;Qtr;Qty
1112;Q1;10
1112;Q2;87
1112;Q3;874
1112;Q4;91
1134;Q1;40
1134;Q3;43


Output: (Key-ID)

ID;Q1QTy;Q2Qty;Q3Qty;Q4Qty
1112;10;87;874;91
1134;40;;43;;


Can some one help me to do this, please?

[logic]

Arn73,
The following post might help you to proceed.
viewtopic.php?t=96215&highlight=

[ray.wurlod]

Welcome aboard, arn73. :D

What you are seeking to do is called a "horizontal pivot", so the suggestion made by logic won't help.

The good news is that what you're trying to do is exactly what the Pivot stage does. Read about it in the Pivot.pdf manual (this is in the Docs folder where your DataStage client is installed). If you have any remaining questions, please feel welcome to post back here.

You might also find this post useful.

[logic]

Hi Arn73, Ray,
I am sorry. Actually I did not read the whole post. I read the following part and actually wanted to point at it.

Code: Select all

You're doing what is called a pivot. You can checkout the Pivot stage

My Bad.

[arn73]

Arn73,
The following post might help you to proceed.
viewtopic.php?t=96215&highlight=

It appear to be slightly different. I have the output file where the columns are the second part of the input file key. Also, the number of columns in the output is fixed.

[arn73]

Thank you Ray, logic. I will refer to the Pivot stage.

[arn73]

I have reviewed the Pivot stage, and what I am trying to do here is vertical pivot (Rows to columns), that is not supported by this stage.

[kcbland]

See here

viewtopic.php?t=96215

[Bala R]

Arn73,
The following post might help you to proceed.
viewtopic.php?t=96215&highlight=

It appear to be slightly different. I have the output file where the columns are the second part of the input file key. Also, the number of columns in the output is fixed.

arn73,
The placement of the column over which you want to pivot does not matter. The advice given in the post pointed to are valid for you. You could search the forum on how to use stage variables for this.
ok.. I did the search and HERE you will find the answer. Its a 2 year old post

[vhshankar]

I do have a solution to convert rows into multiple columns. However, I do it in the database, instead of in the DataStage. I know very well that this might be a trivia here.

For instance, in MS SQL Server, using T-SQL, I wrote a User-Defined function, and I use that function in the SELECT statement, as we would use any normal FieldName. This function transposes the rows to multiple columns, using cursors (not an efficient way, but it works 100 percent).

Again, this may be outside of DataStage, but it works perfectly. And since this is a tested and working solution, I did not waste much time over this in DataStage. Moreover, the Pivot stage only does a horizontal pivot, and NOT a vertical pivot, which most of us are looking for here.

Hari

[kcbland]

A SQL query, using CASE or DECODE statements to logically move information to the appropriate column, using GROUP BY yourkey style logic with MAX() derivations on all CASE derived columns will give you a vertical pivot.

EX:

Code: Select all

select key, max(val_1), max(val_2), max(val_3) from (
select key, 
   case rowtype=1 then value else null end "VAL_1",
   case rowtype=2 then value else null end "VAL_2",
   case rowtype=3 then value else null end "VAL_3"
from yourtable
)
group by key

This approach is not very scalable for a lot of columns, but it's pretty handy. You can also pivot and summary in one swell foop by changing MAX to SUM.