oracle数据 列变行

原始数据：

select a.item_name,
(a.ht_data_item_value/b.ht_data_item_value-1)*100 as ht_daychange,
(a.lh_data_item_value/b.lh_data_item_value-1)*100 as lh_daychange,
(a.zg_data_item_value/b.zg_data_item_value-1)*100 as zg_daychange,
(a.gjzx_data_item_value/b.gjzx_data_item_value-1)*100 as gjzx_daychange,
(a.xgjk_data_item_value/b.xgjk_data_item_value-1)*100 as xgjk_daychange,
(a.qhgs_data_item_value/b.qhgs_data_item_value-1)*100 as qhgs_daychange,
(a.htcx_data_item_value/b.htcx_data_item_value-1)*100 as htcx_daychange,
(a.zjzt_data_item_value/b.zjzt_data_item_value-1)*100 as zjzt_daychange
from ERMP_SOUR.BB6_FXZBJG_SUB a
left join ermp_sour.bb6_fxzbjg_sub b
on a.item_name = b.item_name
left join ermp_sour.bb6_fxzbjg_sub c
on a.item_name = c.item_name
where a.business_date = 20210906 and a.item_name in ('风险覆盖率', '流动性覆盖率', '净稳定资金率', '资本杠杆率')
and b.business_date = 20210903 and b.item_name in ('风险覆盖率', '流动性覆盖率', '净稳定资金率', '资本杠杆率')
and c.business_date = 20210831 and c.item_name in ('风险覆盖率', '流动性覆盖率', '净稳定资金率', '资本杠杆率')
order by a.row_num asc

改变后的数据：

with tmp as (
select a.item_name,
(a.ht_data_item_value/b.ht_data_item_value-1)*100 as ht_daychange,
(a.lh_data_item_value/b.lh_data_item_value-1)*100 as lh_daychange,
(a.zg_data_item_value/b.zg_data_item_value-1)*100 as zg_daychange,
(a.gjzx_data_item_value/b.gjzx_data_item_value-1)*100 as gjzx_daychange,
(a.xgjk_data_item_value/b.xgjk_data_item_value-1)*100 as xgjk_daychange,
(a.qhgs_data_item_value/b.qhgs_data_item_value-1)*100 as qhgs_daychange,
(a.htcx_data_item_value/b.htcx_data_item_value-1)*100 as htcx_daychange,
(a.zjzt_data_item_value/b.zjzt_data_item_value-1)*100 as zjzt_daychange
from ERMP_SOUR.BB6_FXZBJG_SUB a
left join ermp_sour.bb6_fxzbjg_sub b
on a.item_name = b.item_name
left join ermp_sour.bb6_fxzbjg_sub c
on a.item_name = c.item_name
where a.business_date = 20210906 and a.item_name in ('风险覆盖率', '流动性覆盖率', '净稳定资金率', '资本杠杆率')
and b.business_date = 20210903 and b.item_name in ('风险覆盖率', '流动性覆盖率', '净稳定资金率', '资本杠杆率')
and c.business_date = 20210831 and c.item_name in ('风险覆盖率', '流动性覆盖率', '净稳定资金率', '资本杠杆率')
order by a.row_num asc )
select a.value_type item_name,a.risk_coverage,a.capital_leverage,a.liquidity_coverage,a.stable_money,
DECODE(a.value_type, '母公司',1,'华泰联合', 2, '华泰资管', 3, '华泰国际', 4, '期货公司', 5, '华泰创新',6,'股交中心',7,'紫金直投',8,9) as px
from (
select item_name,item_value,value_type from tmp
unpivot (item_value for value_type in (ht_daychange as '母公司',lh_daychange as '华泰联合',zg_daychange as '华泰资管',
gjzx_daychange as '股交中心',xgjk_daychange as '华泰国际',qhgs_daychange as '期货公司',htcx_daychange as '华泰创新',zjzt_daychange as '资金直投')) )
pivot (max(item_value) for item_name in ('风险覆盖率' as risk_coverage,'资本杠杆率' as capital_leverage,'流动性覆盖率' as liquidity_coverage,'净稳定资金率' as stable_money)
) a
ORDER BY px