WITH TEST AS ( SELECT '1' AS BH,'AA' AS NR1,'AA1' AS NR2 FROM DUAL UNION SELECT '1' AS BH,'BB' AS NR1,'BB1' AS NR2 FROM DUAL UNION SELECT '2' AS BH,'AZ' AS NR1,'AZ1' AS NR2 FROM DUAL UNION SELECT '2' AS BH,'SX' AS NR1,'SX1' AS NR2 FROM DUAL ) SELECT BH, MAX(DECODE(ROWNO, 1, NR1, ' ')), MAX(DECODE(ROWNO, 1, NR2, ' ')), MAX(DECODE(ROWNO, 2, NR1, ' ')), MAX(DECODE(ROWNO, 2, NR2, ' ')) FROM (SELECT ROW_NUMBER() OVER(PARTITION BY BH ORDER BY BH) AS ROWNO, BH,NR1,NR2 FROM TEST) GROUP BY BH
SELECT '1' AS BH,'AA' AS NR1,'AA1' AS NR2 FROM DUAL
UNION
SELECT '1' AS BH,'BB' AS NR1,'BB1' AS NR2 FROM DUAL
UNION
SELECT '2' AS BH,'AZ' AS NR1,'AZ1' AS NR2 FROM DUAL
UNION
SELECT '2' AS BH,'SX' AS NR1,'SX1' AS NR2 FROM DUAL
)
SELECT BH,
MAX(DECODE(ROWNO, 1, NR1, ' ')),
MAX(DECODE(ROWNO, 1, NR2, ' ')),
MAX(DECODE(ROWNO, 2, NR1, ' ')),
MAX(DECODE(ROWNO, 2, NR2, ' '))
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY BH ORDER BY BH) AS ROWNO, BH,NR1,NR2
FROM TEST)
GROUP BY BH