DSXchange

Posted: **Tue Nov 26, 2013 1:36 pm**

Hello Everyone,

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 ?

Posted: **Tue Nov 26, 2013 2:37 pm**

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?

Posted: **Tue Nov 26, 2013 2:42 pm**

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.

Posted: **Tue Nov 26, 2013 3:01 pm**

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

Posted: **Tue Nov 26, 2013 3:24 pm**

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.

Posted: **Thu Jan 09, 2014 7:40 am**

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

DSXchange

vertical pivot stage in Datastage 9.1

vertical pivot stage in Datastage 9.1