我有一张类似下图的表格,我需要类似下图的输出,我有一个使用 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 个级别。
在 Oracle-11g 中使用 Unpivot :
它执行列到行的操作,并使用解码来导出其他列的值。
尝试一下:
select distinct /*product_code,*/quantity as ORG_UNIT_CD,
decode(product_code,'cd1','"'||nm1||'"','cd2','"'||nm2||'"','cd3','"'||nm3||'"','cd4','"'||nm4||'"','cd5','"'||nm5||'"') as ORG_UNIT_NM,
decode(product_code,'cd1','0','cd2','cd1','cd3','cd2','cd4','cd3','cd5','cd4') as PARENT_ORG_UNIT_CD
from unpivot_test2
UNPIVOT(quantity FOR product_code in (cd1 as 'cd1',cd2 as 'cd2',cd3 as 'cd3', cd4 as 'cd4', cd5 as 'cd5'))
where quantity <> 0;;