IND | LOCATION |
FROM | 부산 |
FROM | 대구 |
TO | 서울 |
TO | 광주 |
IND컬럼에 FROM과 TO가 둘다 있을 수도 있고 한개씩만 있을 수도 있습니다.
CASE1) TABLE에 FROM만 있을 경우
부산 - ALL
대구 - ALL
CASE2) TABLE에 TO만 있을 경우
ALL-서울
ALL-광주
CASE3)위처럼 FROM, TO가 다 있을경우
부산-서울
대구-서울
부산-광주
대구-광주
TABLE설계가 잘 못된건지 머리르 써봐도 잘 안되네요...
도움 부탁드립니다. 감사합니다.
WITH t AS ( SELECT '' ind, '' location FROM dual WHERE 1=2 UNION ALL SELECT 'FROM', '부산' FROM dual UNION ALL SELECT 'FROM', '대구' FROM dual --UNION ALL SELECT 'TO', '서울' FROM dual --UNION ALL SELECT 'TO', '광주' FROM dual ) SELECT NVL(a.location, 'ALL') location_from , NVL(b.location, 'ALL') location_to FROM (SELECT * FROM t WHERE ind = 'FROM') a FULL OUTER JOIN (SELECT * FROM t WHERE ind = 'TO' ) b ON 1=1 ;
WITH t AS ( SELECT '' ind, '' location FROM dual WHERE 1=2 UNION ALL SELECT 'FROM', '부산' FROM dual UNION ALL SELECT 'FROM', '대구' FROM dual --UNION ALL SELECT 'TO', '서울' FROM dual --UNION ALL SELECT 'TO', '광주' FROM dual ) SELECT CONNECT_BY_ROOT(location) location_from , DECODE(LEVEL, 1, 'ALL', location) location_to FROM t WHERE CONNECT_BY_ISLEAF = 1 START WITH ind = 'FROM' CONNECT BY LEVEL <= 2 AND PRIOR ind != ind ;