데이터베이스 질문과 답변
Table 1 =========== grp name 1000 A1 1000 A2 Table 2 =========== grp name 1000 B1 1000 B2
이런 데이터가 있을때
결과 ============== name lv A1 1 B1 2 B2 2 A2 1 B1 2 B2 2
이와같이 나타내고 싶습니다.
도움부탁드립니다.
감사합니다.
by jkson
[2020.02.05 11:28:19]
WITH t1(cd) AS ( SELECT 'A1' FROM dual UNION ALL SELECT 'A2' FROM dual ) , t2(cd) AS ( SELECT 'B1' FROM dual UNION ALL SELECT 'B2' FROM dual ) SELECT NVL(t2.cd,t1.cd) CD , 2 - GROUPING(T2.CD) LV FROM t1, t2 GROUP BY t1.cd, ROLLUP(t2.cd) ORDER BY t1.cd||t2.cd
by 짱구
[2020.02.05 11:32:04]
정말 감사합니다.
by 마농
[2020.02.05 13:07:00]
WITH t1 AS
(
SELECT 1000 grp, 'A1' name FROM dual
UNION ALL SELECT 1000, 'A2' FROM dual
)
, t2 AS
(
SELECT 1000 grp, 'B1' name FROM dual
UNION ALL SELECT 1000, 'B2' FROM dual
)
, t3 AS
(
SELECT grp, name, 1 lv FROM t1
UNION ALL
SELECT grp, name, 2 lv FROM t2
)
SELECT *
FROM t3
START WITH lv = 1
CONNECT BY PRIOR grp = grp
AND PRIOR lv + 1 = lv
;
by 짱구
[2020.02.05 16:16:14]
항상 감사합니다.
마농님
) 버튼을 클릭하여 작성 하시면 됩니다.