WITH T ( ce_id , mo_id , ord_id , ho_id ) AS ( SELECT 'wo03' , 'model01' ,'g200' ,'A1' FROM DUAL UNION ALL SELECT 'wo03' , 'model01' ,'g200' ,'A2'FROM DUAL UNION ALL SELECT 'wo03' , 'model01' ,'g200' ,'A3'FROM DUAL UNION ALL SELECT 'wo03' , 'model01' ,'g200' ,'A4'FROM DUAL ) SELECT ce_id , mo_id , ord_id , MAX(DECODE(ROWNUM , 1 , ho_id )) ho1 , MAX(DECODE(ROWNUM , 2 , ho_id )) ho2 , MAX(DECODE(ROWNUM , 3 , ho_id )) ho3 , MAX(DECODE(ROWNUM , 4 , ho_id )) ho4 FROM T GROUP BY ce_id , mo_id , ord_id
WITH T ( ce_id , mo_id , ord_id , ho_id ) AS ( SELECT 'wo03' , 'model01' ,'g200' ,'A1' FROM DUAL UNION ALL SELECT 'wo03' , 'model01' ,'g200' ,'A2'FROM DUAL UNION ALL SELECT 'wo03' , 'model01' ,'g200' ,'A3'FROM DUAL UNION ALL SELECT 'wo03' , 'model01' ,'g200' ,'A4'FROM DUAL UNION ALL SELECT 'wo04' , 'model01' ,'g200' ,'A1'FROM DUAL UNION ALL SELECT 'wo04' , 'model01' ,'g200' ,'A2'FROM DUAL UNION ALL SELECT 'wo04' , 'model01' ,'g200' ,'A3'FROM DUAL UNION ALL SELECT 'wo04' , 'model01' ,'g200' ,'A4'FROM DUAL ) -- ho1 , ho2 , ho3 , ho4 들어갈 규칙은 답변자 맘대로.. -- rank는 중복 가능성이.. SELECT ce_id , MAX(mo_id) , MAX(ord_id) , MAX(DECODE(rn , 1 , ho_id )) ho1 , MAX(DECODE(rn , 2 , ho_id )) ho2 , MAX(DECODE(rn , 3 , ho_id )) ho3 , MAX(DECODE(rn , 4 , ho_id )) ho4 FROM (SELECT T.* , ROW_NUMBER() OVER(PARTITION BY ce_id ORDER BY ho_id) rn FROM T ) GROUP BY ce_id
-- 정해진 값인지 아닌지 분명히 답하지 않으시네요... -- 일단 순번을 부여해 나누겠습니다. -- 1. 30개 컬럼으로 분류 SELECT a, b, c, d, e, f, g , MIN(DECODE(rn, 1, h)) h01 , MIN(DECODE(rn, 1, h)) h02 -- 중략 -- , MIN(DECODE(rn, 29, h)) h29 , MIN(DECODE(rn, 30, h)) h30 FROM (SELECT a, b, c, d, e, f, g, h , ROW_NUMBER() OVER(ORDER BY h) rn FROM t ) GROUP BY a, b, c, d, e, f, g ORDER BY a, b, c, d, e, f, g ; -- 2. 한개 컬럼에 구분자로 합치기 SELECT a, b, c, d, e, f, g , LISTAGG(h, ',') WITHIN GROUP(ORDER BY h) h FROM t GROUP BY a, b, c, d, e, f, g ORDER BY a, b, c, d, e, f, g ;