Getting the columns names and corresponding values in rows

[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.

[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
have fun

[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.

[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.