처음에는 쉬운 쿼리인줄 알았는데 ㅜㅜ 고수님들의 조언부탁드립니다.
with test1(num,title) as (select '1' ,'' from dual UNION ALL select '2' ,'김광진 편지' from dual UNION ALL select '3' ,'여기까지가 끝인가보오' from dual UNION ALL select '3' ,'이제나는 돌아서겠소' from dual UNION ALL select '3' ,'억지노력으로 인연을' from dual UNION ALL select '3' ,'거슬러 괴롭히지는' from dual UNION ALL select '3' ,'않겠소 하고싶은말' from dual UNION ALL select '4' ,'하려 했던말' from dual UNION ALL select '4' ,'이대로다 남겨두고서' from dual UNION ALL select '4' ,'혹시나 기대도' from dual UNION ALL select '4' ,'포기하려하오 그대부디' from dual UNION ALL select '5' ,'기나긴 그대' from dual UNION ALL select '5' ,'침묵을 이별로' from dual UNION ALL select '5' ,'받아 두겠소' from dual UNION ALL select '6' ,'응 응으으으으으으' from dual UNION ALL select '6' ,'1ㅈ5ㄹ 0ㅈ6' from dual UNION ALL select '7' ,'' from dual )
↑이게 가상테이블이고요
처음 사진이 원본 데이터이고요.
두번째 사진이 바꿀 데이터 입니다. 각 row의 num 별로 있는 갯수별 출력을하고 싶은데... 생각보다 어렵네요..
select max(decode(num, 1, title)) "1", max(decode(num, 2, title)) "2", max(decode(num, 3, title)) "3", max(decode(num, 4, title)) "4", max(decode(num, 5, title)) "5", max(decode(num, 6, title)) "6", max(decode(num, 7, title)) "7" from (select num, title, row_number() over (partition by num order by rownum) rn from test1) group by rn order by rn
WITH test1(num, seq, title) AS ( SELECT 1, 1, '' from dual UNION ALL SELECT 2, 1, '김광진 편지' FROM dual UNION ALL SELECT 3, 1, '여기까지가 끝인가보오' FROM dual UNION ALL SELECT 3, 2, '이제나는 돌아서겠소' FROM dual UNION ALL SELECT 3, 3, '억지노력으로 인연을' FROM dual UNION ALL SELECT 3, 4, '거슬러 괴롭히지는' FROM dual UNION ALL SELECT 3, 5, '않겠소 하고싶은말' FROM dual UNION ALL SELECT 4, 1, '하려 했던말' FROM dual UNION ALL SELECT 4, 2, '이대로다 남겨두고서' FROM dual UNION ALL SELECT 4, 3, '혹시나 기대도' FROM dual UNION ALL SELECT 4, 4, '포기하려하오 그대부디' FROM dual UNION ALL SELECT 5, 1, '기나긴 그대' FROM dual UNION ALL SELECT 5, 2, '침묵을 이별로' FROM dual UNION ALL SELECT 5, 3, '받아 두겠소' FROM dual UNION ALL SELECT 6, 1, '응 응으으으으으으' FROM dual UNION ALL SELECT 6, 2, '1ㅈ5ㄹ 0ㅈ6' FROM dual UNION ALL SELECT 7, 1, '' FROM dual ) -- 각 num 별로 정렬 기준 항목(seq)이 필요합니다. SELECT * FROM test1 PIVOT (MIN(title) FOR num IN (1, 2, 3, 4, 5, 6, 7)) ORDER BY seq ;