I have a below query:
SELECT COL1,ABC AS COL2,'1' AS SEQ,'GRP 10' AS GRP_NAME,'ABC' AS INPUT_COL_NAME,'NEW1' AS OUTPUT_COL_NAME FROM TABLE1
UNION ALL
SELECT COL1,MNO AS COL2,'2' AS SEQ,'GRP 10' AS GRP_NAME,'MNO' AS INPUT_COL_NAME,'NEW2' AS OUTPUT_COL_NAME FROM TABLE1
UNION ALL
SELECT COL1,XYZ AS COL2,'3' AS SEQ,'GRP 10' AS GRP_NAME,'XYZ' AS INPUT_COL_NAME,'NEW3' AS OUTPUT_COL_NAME FROM TABLE1
INPUT TABLE DATA:
SELECT COL1,COL2 FROM TABLE1
COL1 ABC MNO XYZ 1 0 20 100 2 15 50 150
OUTPUT IS:
COL1 COL2 SEQ GRP_NAME INPUT_COL_NAME OUTPUT_COL_NAME 1 0 1 GRP_NEW ABC NEW1 2 15 1 GRP_NEW ABC NEW1 1 20 2 GRP_NEW MNO NEW2 2 50 2 GRP_NEW MNO NEW2 1 100 3 GRP_NEW XYZ NEW3 2 150 3 GRP_NEW XYZ NEW3
Can above query be transformed into PIVOT query? to generate same output.
You want UNPIVOT (rather than PIVOT):
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
Which, for the sample data:
CREATE TABLE table1 ( COL1, ABC, MNO, XYZ ) AS
SELECT 1, 0, 20, 100 FROM DUAL UNION ALL
SELECT 2, 15, 50, 150 FROM DUAL
Outputs:
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
db<>fiddle here