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.
Getting the columns names and corresponding values in rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
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.
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
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.
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.
Benjamin