안녕하세요 ~
아래와 같은 데이터가 있습니다.
WITH t1(M) AS
( SELECT 'D01' FROM dual
UNION ALL
SELECT 'D02' FROM dual
UNION ALL
SELECT 'D03' FROM dual
UNION ALL
SELECT 'D04' FROM dual
UNION ALL
SELECT 'D05' FROM dual
.
.
.
n
)
WITH t2(L) AS
( SELECT 'L01' FROM dual
UNION ALL
SELECT 'L02' FROM dual
UNION ALL
SELECT 'L03' FROM dual
UNION ALL
SELECT 'L04' FROM dual
UNION ALL
SELECT 'L05' FROM dual
UNION ALL
SELECT 'L06' FROM dual
.
.
.
n
)
이러한 데이터를 가지고 랜덤으로 dispatching을 하는 기능입니다.
원하는 결과는 :
t1.m, t2.l
D01 L01,L06
D02 L02
D03 L03
D04 L04
D05 L05
앞에 t1.m의 값을 개수에 따라 뒤에 오는 t2.l을 주는 겁니다.
고수 님들 도와 주세욤~
-- 음.. 의도한게 맞는지 모르겠네요. WITH T1(M) AS (SELECT 'D' || LPAD(LEVEL, 2, '0') FROM DUAL CONNECT BY LEVEL <= 5) ,T2(L) AS (SELECT 'L' || LPAD(LEVEL, 2, '0') FROM DUAL CONNECT BY LEVEL <= 61) SELECT M ,LISTAGG(L, ' ') WITHIN GROUP(ORDER BY L) FROM (SELECT M ,L ,M_RNK FROM (SELECT M ,COUNT(1) OVER() M_CNT ,ROW_NUMBER() OVER(ORDER BY M) - 1 M_RNK FROM T1) T1 ,(SELECT L ,ROW_NUMBER() OVER(ORDER BY L) - 1 L_RNK FROM T2) T2 WHERE T1.M_RNK = MOD(T2.L_RNK, T1.M_CNT)) GROUP BY M ,M_RNK;
WITH
T1(M) AS (SELECT 'D' || LPAD(LEVEL, 2, '0') FROM DUAL CONNECT BY LEVEL <= 5)
,T2(L) AS (SELECT 'L' || LPAD(LEVEL, 2, '0') FROM DUAL CONNECT BY LEVEL <= 61)
SELECT t1.m , LISTAGG(t2.l, ',') WITHIN GROUP (ORDER BY t2.l)
FROM
( select rownum num , t1.* from t1 ) t1 ,
( select rownum num , MOD(rownum - 1, (select count(1) from t1 )) + 1 as c , t2.* from t2 ) t2
WHERE t1.num = t2.c
group by t1.m