데이터베이스 질문과 답변
한 테이블에서 특정 분류 조건에 의하여 값을 ROW DATA가 아닌 컬럼으로 펼쳐서 표기해야합니다. FULL OUTER JOIN으로 해결하려 했으나 JOIN 기준에 따라 NULL 값이 생겨서 원하는 결과 값을 못 구하고 있습니다.. (조건에 따른 데이터 건수가 유동적이라서 JOIN 기준이 애매한 상황입니다)
| 현재 쿼리 결과 값 | 원하는 결과 값 | ||||||||||||
| SUB1 | SUB_NM1 | SUB2 | SUB_NM2 | SUB3 | SUB_NM3 | SUB1 | SUB_NM1 | SUB2 | SUB_NM2 | SUB3 | SUB_NM3 | ||
| A001 | 교양1 | A002 | 교양2 | B001 | 전공1 | A001 | 교양1 | A002 | 교양2 | B001 | 전공1 | ||
| A003 | 교양2 | => | A003 | 교양2 | B002 | 전공1 | |||||||
| A004 | 교양2 | A004 | 교양2 | B003 | 전공1 | ||||||||
| B002 | 전공1 | ||||||||||||
| B003 | 전공1 | ||||||||||||
with DS as
( SELECT 'A001' AS SUB, '교양1' AS SUB_NM FROM DUAL
UNION
SELECT 'A002' AS SUB, '교양2' AS SUB_NM FROM DUAL
UNION
SELECT 'A003' AS SUB, '교양2' AS SUB_NM FROM DUAL
UNION
SELECT 'A004' AS SUB, '교양2' AS SUB_NM FROM DUAL
UNION
SELECT 'B001' AS SUB, '전공1' AS SUB_NM FROM DUAL
UNION
SELECT 'B002' AS SUB, '전공1' AS SUB_NM FROM DUAL
UNION
SELECT 'B003' AS SUB, '전공1' AS SUB_NM FROM DUAL
)
,D1_s as
(SELECT * FROM DS WHERE SUB_NM = '교양1'
)
,D2_s as
(SELECT * FROM DS WHERE SUB_NM = '교양2'
)
,D3_s as
(SELECT * FROM DS WHERE SUB_NM = '전공1'
)
, d1 as (select d1_s.*, rownum rn from d1_s )
, d2 as (select d2_s.*, rownum rn from d2_s )
, d3 as (select d3_s.*, rownum rn from d3_s )
SELECT D1.SUB AS SUB1
, D1.SUB_NM AS SUB_NM1
, D2.SUB AS SUB2
, D2.SUB_NM AS SUB_NM2
, D3.SUB AS SUB3
, D3.SUB_NM AS SUB_NM3
FROM d1
FULL OUTER JOIN
d2
ON D1.rn = D2.rn
FULL OUTER JOIN
d3
ON D1.rn = D3.rn
by 마농
[2022.02.16 10:51:51]
WITH ds AS
(
SELECT 'A001' sub, '교양1' sub_nm FROM dual
UNION ALL SELECT 'A002', '교양2' FROM dual
UNION ALL SELECT 'A003', '교양2' FROM dual
UNION ALL SELECT 'A004', '교양2' FROM dual
UNION ALL SELECT 'B001', '전공1' FROM dual
UNION ALL SELECT 'B002', '전공1' FROM dual
UNION ALL SELECT 'B003', '전공1' FROM dual
)
-- sub_nm 이 고정인 경우 --
SELECT rn
, MIN(DECODE(sub_nm, '교양1', sub )) sub_1
, MIN(DECODE(sub_nm, '교양1', sub_nm)) sub_nm_1
, MIN(DECODE(sub_nm, '교양2', sub )) sub_2
, MIN(DECODE(sub_nm, '교양2', sub_nm)) sub_nm_2
, MIN(DECODE(sub_nm, '전공1', sub )) sub_3
, MIN(DECODE(sub_nm, '전공1', sub_nm)) sub_nm_3
FROM (SELECT sub, sub_nm
, ROW_NUMBER() OVER(PARTITION BY sub_nm ORDER BY sub) rn
FROM ds
WHERE sub_nm IN ('교양1', '교양2', '전공1')
)
GROUP BY rn
ORDER BY rn
;
WITH ds AS
(
SELECT 'A001' sub, '교양1' sub_nm FROM dual
UNION ALL SELECT 'A002', '교양2' FROM dual
UNION ALL SELECT 'A003', '교양2' FROM dual
UNION ALL SELECT 'A004', '교양2' FROM dual
UNION ALL SELECT 'B001', '전공1' FROM dual
UNION ALL SELECT 'B002', '전공1' FROM dual
UNION ALL SELECT 'B003', '전공1' FROM dual
)
-- sub_nm 이 가변인 경우 --
SELECT rn
, MIN(DECODE(dr, 1, sub )) sub_1
, MIN(DECODE(dr, 1, sub_nm)) sub_nm_1
, MIN(DECODE(dr, 2, sub )) sub_2
, MIN(DECODE(dr, 2, sub_nm)) sub_nm_2
, MIN(DECODE(dr, 3, sub )) sub_3
, MIN(DECODE(dr, 3, sub_nm)) sub_nm_3
FROM (SELECT sub, sub_nm
, DENSE_RANK() OVER(ORDER BY sub_nm) dr
, ROW_NUMBER() OVER(PARTITION BY sub_nm ORDER BY sub) rn
FROM ds
)
GROUP BY rn
ORDER BY rn
;
by 정시퇴근
[2022.02.16 11:13:07]
제가 접근 자체를 잘 못했군요;;; 감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.