unpivot用法

=====1 測試

select * from (  SELECT
        REMAIN_POOR_FAM as 返貧人口
        ,POOR_FAM as 貧困人口
        ,REMAIN_POOR_POP as 返貧人數
        ,POOR_POP as 貧困人數       
        FROM R_ANALYSIS_HOME_PAGE_Y R
        WHERE STAT_TIME = 2019
        AND PAC = 460000000000)
  unpivot( 值 for 屬性 in (返貧人口,貧困人口,返貧人數,貧困人數)) bcode

原例blog

SELECT 
        INDUSTRY_EXCP1_FAM AS 有納稅
        , INDUSTRY_EXCP2_FAM AS 戶籍問題
        , decode(INDUSTRY_EXCP1_FAM,0,1,round(INDUSTRY_EXCP1_APPEAL_FAM/INDUSTRY_EXCP1_FAM ,4))*100 有納稅1
        ,decode(INDUSTRY_EXCP2_FAM,0,1,round(INDUSTRY_EXCP2_APPEAL_FAM/INDUSTRY_EXCP2_FAM ,4))*100 戶籍問題1
from
        R_DW_INDUSTRY_FAM_M t
        WHERE STAT_TIME = 201906
        AND PAC = 460000000000class

=====2select

select TEST1,字段名,值,RATE from ( 
SELECT  '測試' as TEST1
        ,INDUSTRY_EXCP1_FAM AS 有納稅
        , INDUSTRY_EXCP2_FAM AS 戶籍問題
        , decode(INDUSTRY_EXCP1_FAM,0,1,round(INDUSTRY_EXCP1_APPEAL_FAM/INDUSTRY_EXCP1_FAM ,4))*100 有納稅1
        ,decode(INDUSTRY_EXCP2_FAM,0,1,round(INDUSTRY_EXCP2_APPEAL_FAM/INDUSTRY_EXCP2_FAM ,4))*100 戶籍問題1
            from
        R_DW_INDUSTRY_FAM_M t
        WHERE STAT_TIME = 201906
        AND PAC = 460000000000
）unpivot(值 for 字段名 in (有納稅,戶籍問題 )) b
unpivot (Rate for 字段名1 in (有納稅1,戶籍問題1 )) c
WHERE 字段名=substr(字段名1,1,length(字段名1)-1)im

====3img

select 字段名,值,RATE from ( 
SELECT 
        INDUSTRY_EXCP1_FAM AS 有納稅
        , INDUSTRY_EXCP2_FAM AS 戶籍問題
        , decode(INDUSTRY_EXCP1_FAM,0,1,round(INDUSTRY_EXCP1_APPEAL_FAM/INDUSTRY_EXCP1_FAM ,4))*100 有納稅1
        ,decode(INDUSTRY_EXCP2_FAM,0,1,round(INDUSTRY_EXCP2_APPEAL_FAM/INDUSTRY_EXCP2_FAM ,4))*100 戶籍問題1
from
        R_DW_INDUSTRY_FAM_M t
        WHERE STAT_TIME = 201906
        AND PAC = 460000000000
）unpivot(值 for 字段名 in (有納稅,戶籍問題 )) b
unpivot (Rate for 字段名1 in (有納稅1,戶籍問題1 )) c
WHERE 字段名=substr(字段名1,1,length(字段名1)-1)co