1 2 3 4 5 6 7 8 9 10 11 | SELECT id , MIN (DECODE(status, 'C' , status)) status_c , MIN (DECODE(status, 'C' , amount)) amount_c , MIN (DECODE(status, 'C' , 기간)) 기간_c , MIN (DECODE(status, 'H' , status)) status_h , MIN (DECODE(status, 'H' , amount)) amount_h , MIN (DECODE(status, 'H' , 기간)) 기간_h FROM t GROUP BY id ORDER BY id ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- 방법 추가 / 이 경우 데이타 중복이 2건 미만 아니면 데이타 엉터리로 출력됨. WITH T(V1, V2, V3, V4) AS ( SELECT 1, 'H' , 100, '2012-06-05' FROM DUAL UNION ALL SELECT 1, 'C' , 150, '2012-10-20' FROM DUAL UNION ALL SELECT 2, 'H' , 100, '2012-06-05' FROM DUAL UNION ALL SELECT 2, 'H' , 100, '2012-06-05' FROM DUAL UNION ALL SELECT 4, 'H3' , 100, '2012-06-05' FROM DUAL UNION ALL SELECT 4, 'H33' , 100, '2012-06-05' FROM DUAL UNION ALL SELECT 3, 'H' , 200, '2012-06-05' FROM DUAL ) SELECT T1.V1, T1.V2, T1.V3, T2.V2, T2.V3 FROM ( SELECT ROWNUM RN, T.* FROM T) T1, ( SELECT ROWNUM RN, T.* FROM T) T2 WHERE T1.V1 = T2.V1(+) AND T1.RN != T2.RN(+) AND MOD(T1.RN, 2) = 1 |