如何将 UNPIVOT 拆分成多个列？
40
0
我有一张类似下图的表格，我需要类似下图的输出，我有一个使用 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 个级别。
