Using the Pivot Stage

[KeithM]

I am creating a job to convert a single row into multiple rows so I am using the pivot stage. My input looks like this:

Key Col1 Col2 Col3
A 1 2 3
B 4 5 6

My desired results are:

Key Col Value
A Col1 1
A Col2 2
A Col3 3
B Col1 4
B Col2 5
B Col3 6

I am able to do the horizontal split but how can I populate the 'Col' value in my results so that I can keep track of what my value represents?

[crouse]

In a transformer that follows the pivot, use stage variables to keep a counter that contiunally counts to 3 by 1, concatenating that stage variable value to the "Col" on the output link.

[ogmios]

Or if you start from a database table do it in the database:

Code: Select all

SELECT KEY, 'Col1' AS COL, COL1
FROM TABLE
UNION
SELECT KEY, 'Col2' AS COL, COL2
FROM TABLE
UNION
SELECT KEY, 'Col3' AS COL, COL3
FROM TABLE
ORDER BY KEY, COL

This will beat Datastage in speed anytime* .

Ogmios

* for between 0 and about 1.000.000 rows depending on the kind of database :D

[KeithM]

If I use a counter in the transformer, would it be guaranteed that the order of the rows would always match the order that the columns are in? So would the value for Col1 always come through first followed by Col2, Col3, and so on?

[crouse]

It should. Make sure row buffering is NOT enabled.