How to get the column names along with the rows by using pivot stage in datastage

Question

I have a table in format

Data: 
ID Sev1 Sev2 Sev3 
ABC 0.45 1 1 
PQR 0.45 1 2 
XYZ 0.45 1 1

I want to change this to the new format as below by using horizontal pivot . How can i get column names ( severity ) as well along with its data

Expected Output:

    ID Severity Values 
    ABC Sev1 0.45 
    ABC Sev2 1 
    ABC Sev3 1 
    PQR Sev1 0.45 
    PQR Sev2 1 
    PQR Sev3 2 
    XYZ Sev1 0.45 
    XYZ Sev2 1 
    XYZ Sev3 1

To bring the column names as rows and add along with the corresponding values

After converting using horizontal pivot and after then i am using CDC stage to define if its an insert or update record with CDC_Changecode = 1 or CdcChangeCode = 3. Please find below sample data

    ID Severity Values CDCCode
    ABC Sev1 0.45   1
    ABC Sev2 1      1
    ABC Sev3 1      1
    PQR Sev1 0.45   3
    PQR Sev2 1      3
    PQR Sev3 2      3
    XYZ Sev1 0.45   3
    XYZ Sev2 1      3
    XYZ Sev3 1      3

Once i get this output after using CDC stage ( 1 = Insert record , 3 = Update record ) then i want to convert them columns to rows using vertical pivot. When

Expected output after vertical pivot

CDC Code = 1 ( insert record - i am trying to get below data as ouput )

    ID  Sev1   Sev2 Sev3
    ABC 0.45   1    
    PQR        1

CDC Code = 3 ( Update record - i am trying to get below data as ouput )

    ID  Sev1   Sev2 Sev3
    ABC             1    
    PQR 0.45        2   
    XYZ 0.45   1    1

This requirement seems a little odd to me - could you share with us what you want to achieve in the end? What is the business requirement you are trying to meet? — MichaelTiefenbacher, May 28 '19 at 16:45

score 0 · Accepted Answer · answered May 27 '19 at 19:37

0

Check the Pivot Index checkbox on the Pivot Properties tab.
Then add the additional column to your output on the mapping tab. This will generate a column with a number - the index of the column pivoted.
In a additional step you could then transform the numbers back into column names

answered May 27 '19 at 19:37

MichaelTiefenbacher

3,805
2
11
17

Thanks a lot michael. Sorry for the incomplete question. I have added some more information on top.. Please have a look and please help me with your suggestion. – avinash May 28 '19 at 07:37

How to get the column names along with the rows by using pivot stage in datastage

1 Answers1