vertical pivot stage in Datastage 9.1

[sriec12]

Hello Everyone,

Code: Select all

Input
----------------------------

ST_ID        CT_YS     CT_ID
------------------------------
    1          XY1       100     
    1          ZX1       200     
    1          YZ1       400     
    1          YZ1       300     
    1          YZ1       100     
    

Expected Output
-------------------------------
ST_ID           XY_ID             ZX_ID             YZ_ID    
------------------------------------------------------------
    1             100               200               400
    1             100               200               300
    1             100               200               100

I am not sure how to use for vertical pivot stage?

In vertical pivot stage there is group by and pivot option.......I am not sure how to use it exactly in this case ?

Please guide me ?

[asorrell]

I find your example insufficient (for me at least) to give any sort of answer.

• Can there be multiple entries for "XY1" and / or multiple entries for "ZX1"? If so, what is supposed to happen?
  Are you always going to have exactly three indicators (XY1 / ZX1 / YZ1) and are they guaranteed to always be present and have those exact names?

[bart12872]

First of all, you have to consider the number of distinct occurrences in CT_YS column.
If this number is variable or unlimited, you want an output with variable or unlimited columns, which is not possible in a vertical pivot stage.

You can only use vertical pivot stage if the number of occurrence is defined. In that case, you will set in the Array size in Pivot Definitions with this number.

[sriec12]

@bart12872 ...yes I will get only XY1, ZX1 and YZ1..................Whatever these Id numbers should be stored as in vertical colmns

[bart12872]

so the vertical pivot stage is the stage you need.

But, in your exemple you have "genarate" 4 information

Code: Select all

ST_ID           XY_ID             ZX_ID             YZ_ID    
------------------------------------------------------------ 
    1             100 (input)    200 (input)    400 (input)
    1             100 (gener.)   200 (gener.)   300 (input) 
    1             100 (gener.)   200 (gener.)   100 (input)

so as the pivot stage doesn't "create" information, you will have to do by your own before using the pivot stage.

[dunquach]

But if you have multiple of ZX1, what is the ouput expected.