By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 ( COL1, ABC, MNO, XYZ ) AS
SELECT 1, 0, 20, 100 FROM DUAL UNION ALL
SELECT 2, 15, 50, 150 FROM DUAL
2 rows affected
SELECT col1,
col2,
seq,
'GRP_NEW' AS grp_name,
input_col_name,
output_col_name
FROM table1
UNPIVOT (
input_col_name FOR ( col2, seq, output_col_name ) IN (
abc AS ( 'ABC', 1, 'NEW1' ),
mno AS ( 'MNO', 2, 'NEW2' ),
xyz AS ( 'XYZ', 3, 'NEW3' )
)
)
ORDER BY seq, col1;
| COL1 | COL2 | SEQ | GRP_NAME | INPUT_COL_NAME | OUTPUT_COL_NAME |
|---|---|---|---|---|---|
| 1 | ABC | 1 | GRP_NEW | 0 | NEW1 |
| 2 | ABC | 1 | GRP_NEW | 15 | NEW1 |
| 1 | MNO | 2 | GRP_NEW | 20 | NEW2 |
| 2 | MNO | 2 | GRP_NEW | 50 | NEW2 |
| 1 | XYZ | 3 | GRP_NEW | 100 | NEW3 |
| 2 | XYZ | 3 | GRP_NEW | 150 | NEW3 |