최대 개수만큼 하드코딩 하시면 됩니다만...
현재 세로로 나오는 상태가 훨씬 더 보기 좋은데
왜 굳이 보기 힘들게 가로로 바꾸려는지 모르겠네요?
WITH t AS ( SELECT 111111 id, 1 seqno, '2000-01-01' dat1, '2000-02-01' dat2, 1000 amt1, 1100 amt2 FROM dual UNION ALL SELECT 111111, 2, '2000-01-02', '2000-02-02', 2000, 1200 FROM dual UNION ALL SELECT 111111, 3, '2000-01-03', '2000-02-03', 3000, 1300 FROM dual UNION ALL SELECT 111111, 4, '2000-01-04', '2000-02-04', 4000, 1400 FROM dual UNION ALL SELECT 111111, 5, '2000-01-05', '2000-02-05', 5000, 1500 FROM dual UNION ALL SELECT 111111, 6, '2000-01-06', '2000-02-06', 6000, 1600 FROM dual UNION ALL SELECT 111111, 7, '2000-01-07', '2000-02-07', 7000, 1700 FROM dual UNION ALL SELECT 111111, 8, '2000-01-08', '2000-02-08', 8000, 1800 FROM dual UNION ALL SELECT 111111, 9, '2000-01-09', '2000-02-09', 9000, 1900 FROM dual UNION ALL SELECT 111111, 10, '2000-01-10', '2000-02-10', 10000, 2000 FROM dual UNION ALL SELECT 222222, 1, '2000-01-11', '2000-02-11', 100000, 110000 FROM dual UNION ALL SELECT 222222, 2, '2000-01-12', '2000-02-12', 200000, 120000 FROM dual UNION ALL SELECT 222222, 3, '2000-01-13', '2000-02-13', 300000, 130000 FROM dual UNION ALL SELECT 222222, 4, '2000-01-14', '2000-02-14', 400000, 140000 FROM dual UNION ALL SELECT 333333, 3, '2000-01-15', '2000-02-15', 100, 200 FROM dual UNION ALL SELECT 333333, 4, '2000-01-16', '2000-02-16', 200, 300 FROM dual ) SELECT * FROM (SELECT id, dat1, dat2, amt1, amt2 , ROW_NUMBER() OVER(PARTITION BY id ORDER BY seqno) rn FROM t ) PIVOT (MIN(dat1) dat1, MIN(dat2) dat2, MIN(amt1) amt1, MIN(amt2) amt2 FOR rn IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 , 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 -- 최대 개수만큼 추가 -- ) ) ;
SELECT id , LISTAGG(dat1 ||', '|| dat2 ||', '|| LPAD(amt1, 6, ' ') ||', '|| LPAD(amt2, 6, ' ') , ' | ') WITHIN GROUP(ORDER BY seqno) contents FROM t GROUP BY id ;