Page 1 of 1
Getting the columns names and corresponding values in rows
Posted: Thu Feb 18, 2016 4:07 pm
by anudeepdasari001
I have a table in format
Code:
ID Sev 1 Sev 2 Sev 3
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
Code:
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 the corresponding values.
Posted: Thu Feb 18, 2016 4:31 pm
by cdp
If you are using the Enterprise edition of DataStage, the Pivot Stage could help you there.
Pivot type: horizontal
Group by: ID
Pivot on: Sev 1, Sev2, Sev3
Or it might be the other way around, haven't used it for a while
![Embarassed :oops:](./images/smilies/icon_redface.gif)
have fun
Posted: Fri Feb 19, 2016 3:36 am
by ShaneMuir
Are your column names static or variable? IF static then you just need to hard code some values and then (as cdp suggests) pivot the data.
If your column names and the number of columns is variable then you need to get creative and read the metadata for the table first to get the column name values.
Re: Getting the columns names and corresponding values in ro
Posted: Mon Feb 22, 2016 5:33 am
by Benz
Step 1: Do a picot for the column names
Separate the column names as a data and do a pivot
Expected ouput:
Col1 Col2 Pivot_Index
ID Sev1 0
ID Sev2 1
ID Sev3 2
Step 2 Do a pivot for the whole data
Expected ouput:
ID Pivot_Index Values
ABC 0 0.45
ABC 1 1
ABC 2 1
PQR 0 0.45
PQR 1 1
PQR 2 2
XYZ 0 0.45
XYZ 1 1
XYZ 2 1
Step 3: Join both the data using the pivot_index Column.
I hope it works.