我有一张类似下图的表格,我需要类似下图的输出,我有一个使用 union 的查询来获取所需的输出,选择 different cd_5 作为 ORG_UNIT_CD,upper('\''||nm_5||'\'') 作为 ORG_U...
我有一张如下图所示的表格
我需要像下图这样的输出
我有一个使用 union 来获取所需输出的查询
select distinct cd_5 as ORG_UNIT_CD, upper('"'||nm_5||'"') as ORG_UNIT_NM, cd4 as PARENT_ORG_UNIT_CD, 5 as ORG_UNIT_LEVEL
from TBL
where cd_5 <> 0
union
select distinct cd_4 as ORG_UNIT_CD, upper('"'||nm_4||'"') as ORG_UNIT_NM, cd_3 as PARENT_ORG_UNIT_CD, 4 as ORG_UNIT_LEVEL
from TBL
where cd_4 <> 0
union
select distinct cd_3 as ORG_UNIT_CD, upper('"'||nm_3||'"') as ORG_UNIT_NM, cd_2 as PARENT_ORG_UNIT_CD, 3 as ORG_UNIT_LEVEL
from TBL
where cd_3 <> 0
union
select distinct cd_2 as ORG_UNIT_CD, upper('"'||nm_2||'"') as ORG_UNIT_NM, cd_1 as PARENT_ORG_UNIT_CD, 2 as ORG_UNIT_LEVEL
from TBL
where cd_2 <> 0
union
select distinct cd_1 as ORG_UNIT_CD, upper('"'||nm_1||'"') as ORG_UNIT_NM, 0 as PARENT_ORG_UNIT_CD, 1 as ORG_UNIT_LEVEL
from TBL
where cd_1 <> 0
order by ORG_UNIT_LEVEL
我需要一个简化的查询来代替这个。因为我只给出了五个级别的层次结构,但实际上我有 20 个级别。
我怀疑你正在追求以下类似的东西:
with dummy as (select 1 id
from dual
connect by level <= 5)
select case when d.id = 1 then t.cd_1
when d.id = 2 then t.cd_2
when d.id = 3 then t.cd_3
when d.id = 4 then t.cd_4
when d.id = 5 then t.cd_5
end org_unit_cd,
case when d.id = 1 then t.nm_1
when d.id = 2 then t.nm_2
when d.id = 3 then t.nm_3
when d.id = 4 then t.nm_4
when d.id = 5 then t.nm_5
end org_unit_nm,
case when d.id = 1 then 0
when d.id = 2 then t.cd_1
when d.id = 3 then t.cd_2
when d.id = 4 then t.cd_3
when d.id = 5 then t.cd_4
end parent_org_unit_cd,
d.id org_unit_level
from (select distinct cd_1,
cd_2,
cd_3,
cd_4,
cd_5,
'"'||upper(nm_1)||'"' nm_1,
'"'||upper(nm_2)||'"' nm_2,
'"'||upper(nm_3)||'"' nm_3,
'"'||upper(nm_4)||'"' nm_4,
'"'||upper(nm_5)||'"' nm_5
from tbl) t
cross join dummy d
where case when d.id = 1 then t.cd_1
when d.id = 2 then t.cd_2
when d.id = 3 then t.cd_3
when d.id = 4 then t.cd_4
when d.id = 5 then t.cd_5
end != 0;
这只是将 TBL 中的每个不同行复制 5 次,并在相关行上输出每个 cd_N、nm_N 和 cd_n-1。以上操作在 10g 和 11g 中均有效。
以下答案在 11g 中有效:
select org_unit_cd,
org_unit_nm,
parent_org_unit_cd,
org_unit_level
from (select distinct cd_1,
cd_2,
cd_3,
cd_4,
cd_5,
'"'||upper(nm_1)||'"' nm_1,
'"'||upper(nm_2)||'"' nm_2,
'"'||upper(nm_3)||'"' nm_3,
'"'||upper(nm_4)||'"' nm_4,
'"'||upper(nm_5)||'"' nm_5,
0 cd_0
from tbl)
unpivot ((org_unit_cd, org_unit_nm, parent_org_unit_cd) for org_unit_level in ((cd_1, nm_1, cd_0) as 1,
(cd_2, nm_2, cd_1) as 2,
(cd_3, nm_3, cd_2) as 3,
(cd_4, nm_4, cd_3) as 4,
(cd_5, nm_5, cd_4) as 5))