자료가 출력될때 항상 특정배수를 기준으로 행을 동일하게 반환할수 있는 방법이 있을까요? 5의 배수라면 자료가 3줄이며 5줄반환, 11줄이면 15줄반환하는 형태로... FULL JOIN으로 가능할줄 알았는데 DATA 값이 A만 입력되면 문제없는 A,B로 여러개 입력되니 원하는 형태로 안나오네요. 조언 좀 부탁드립니다. WITH W_DATA AS ( SELECT ROW_NUMBER() OVER (PARTITION BY DATA, TRUNC((ROWSEQ -1)/ 5) ORDER BY DATA) RN , A.* FROM (SELECT ROW_NUMBER() OVER (PARTITION BY DATA ORDER BY DATA) ROWSEQ , T.* FROM (SELECT 'A' AS DATA FROM DUAL UNION ALL SELECT 'B' AS DATA FROM DUAL UNION ALL SELECT 'B' AS DATA FROM DUAL UNION ALL SELECT 'B' AS DATA FROM DUAL UNION ALL SELECT 'B' AS DATA FROM DUAL UNION ALL SELECT 'B' AS DATA FROM DUAL) T )A ) , W_SEQ AS ( SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 5 ) SELECT A.RN, A.DATA FROM W_DATA A FULL OUTER JOIN W_SEQ B ON A.RN = B.LV ## 현재 값 ## ## 원하는 값 ## 1 A 1 A 1 B 2 A 2 B 3 A 3 B 4 A 4 B 5 A 5 B 1 B 2 B 3 B 4 B 5 B
WITH t AS ( SELECT 'a' data FROM dual UNION ALL SELECT 'b' FROM dual UNION ALL SELECT 'b' FROM dual UNION ALL SELECT 'b' FROM dual UNION ALL SELECT 'b' FROM dual UNION ALL SELECT 'b' FROM dual UNION ALL SELECT 'b' FROM dual ) SELECT x * 5 + lv rn , data FROM (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) a LEFT OUTER JOIN (SELECT CEIL(ROW_NUMBER() OVER(PARTITION BY data ORDER BY 1) / 5)-1 x , MOD (ROW_NUMBER() OVER(PARTITION BY data ORDER BY 1)-1, 5)+1 y , data FROM t ) b PARTITION BY (data, x) ON lv = y ;