with org AS (
select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1234' as code, 'RMI1' as code_name from dual union all
select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1235' as code, 'RMI2' as code_name from dual union all
select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1236' as code, 'RMI3' as code_name from dual union all
select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB1111' as code, 'RMI1' as code_name from dual union all
select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB2222' as code, 'RMI2' as code_name from dual
) select * from org;
위와 같은 org 테이블이 있다고 가정합니다.
pcode 컬럼의 데이터는 무조건 2레벨 데이터이고 code 컬럼이 3레벨 데이터 입니다.
2레벨의 상위(pcode)코드는 항상 'S100'이고 3레벨의 상위(pcode)는 같은 행의 pcode를 따라가야 합니다.
그리고 ord 값은 order by lvl, pcode, code 순으로 정렬해서 순서값을 매겨주면 됩니다.
이럴경우 아래와 같이 쿼리를 하였는데... 좀 더 깔끔한(?) 방법이 없을까요?
고수님들의 조언 부탁드립니다.
with org AS (
select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1234' as code, 'RMI1' as code_name from dual union all
select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1235' as code, 'RMI2' as code_name from dual union all
select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1236' as code, 'RMI3' as code_name from dual union all
select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB1111' as code, 'RMI1' as code_name from dual union all
select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB2222' as code, 'RMI2' as code_name from dual
)
select
lvl
, code
, code_name
, pcode
, rownum as ord
from
(
select
lvl
, code
, code_name
, pcode
from
(
select 2 as lvl
, pcode as code
, pcode_name as code_name
, 'S100' as pcode
from (
select distinct pcode, pcode_name
from org
)
UNION ALL
select 3 as lvl
, code as code
, code_name as code_name
, pcode as pcode
from (
select distinct pcode, pcode_name, code, code_name
from org
)
)
order by
lvl
, pcode
, code
)
-- 그냥.. 똑같이만.. with org AS ( select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1234' as code, 'RMI1' as code_name from dual union all select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1235' as code, 'RMI2' as code_name from dual union all select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1236' as code, 'RMI3' as code_name from dual union all select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB1111' as code, 'RMI1' as code_name from dual union all select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB2222' as code, 'RMI2' as code_name from dual ) SELECT LV , CODE , CODE_NAME , PCODE , ROW_NUMBER() OVER(ORDER BY LV , PCODE , CODE ) RN FROM (SELECT DISTINCT 2 AS LV , PCODE CODE , PCODE_NAME AS CODE_NAME, 'S100' AS PCODE FROM ORG UNION ALL SELECT 3 AS LV , CODE , CODE_NAME , PCODE FROM ORG ) A ORDER BY LV , PCODE , CODE
WITH org AS ( SELECT 'D022' pcode, 'A대리점' pcode_name, 'AP1234' code, 'RMI1' code_name FROM dual UNION ALL SELECT 'D022', 'A대리점', 'AP1235', 'RMI2' FROM dual UNION ALL SELECT 'D022', 'A대리점', 'AP1236', 'RMI3' FROM dual UNION ALL SELECT 'D035', 'B대리점', 'AB1111', 'RMI1' FROM dual UNION ALL SELECT 'D035', 'B대리점', 'AB2222', 'RMI2' FROM dual ) -- UNION -- SELECT lvl , code , code_name , pcode , ROWNUM ord FROM (SELECT 3 lvl, pcode, code, code_name FROM org UNION -- 유니온을 이용한 중복 제거 및 정렬 -- SELECT 2, 'S100', pcode, pcode_name FROM org ) ; -- ROLLUP -- SELECT lvl , code , code_name , pcode , ROWNUM ord FROM (SELECT NVL2(code, 3, 2) lvl , NVL(code, pcode) code , NVL(code_name, pcode_name) code_name , NVL2(code, pcode, 'S100') pcode FROM org GROUP BY pcode, pcode_name, ROLLUP((code, code_name)) ORDER BY lvl, pcode, code ) ; -- PIVOT -- SELECT lvl , code , code_name , pcode , ROWNUM ord FROM (SELECT DISTINCT * FROM (SELECT 'S100' ppcode , pcode, pcode_name, code, code_name FROM org ) UNPIVOT ((code, code_name, pcode) FOR lvl IN ( ( code, code_name, pcode) AS 3 , (pcode, pcode_name, ppcode) AS 2 ) ) ORDER BY lvl, pcode, code ) ; -- 복제 -- SELECT lvl , code , code_name , pcode , ROWNUM ord FROM (SELECT DISTINCT lvl , DECODE(lvl, 2, pcode, code) code , DECODE(lvl, 2, pcode_name, code_name) code_name , DECODE(lvl, 2, 'S100', pcode) pcode FROM org , (SELECT LEVEL + 1 lvl FROM dual CONNECT BY LEVEL <= 2) ORDER BY lvl, code, pcode ) ;