oracle 行转列函数pivot和unpivot

oracle 行转列函数pivot和unpivot

　　今天接到业务部门的一个需求，需要对同一公司的不同财务指标进行排序，需要用到oracle的行转列函数unpivot。

财务报表的表结构为：

要实现业务部门的排序筛选功能，需要首先将行数据转为列数据：

使用unpivot函数，实现如下

with t4 as (select t3.COMPCODE,

       t3.ENDDATE,

       t3.CURFDS        as "货币资金",

       t3.TRADFINASSET  as "交易性金融资产",

       t3.NOTESRECE     as "应收票据",

       t3.PLAC          AS "拆出资金",

       T3.DERIFINAASSET AS "衍生金融资产",

       T3.ACCORECE      AS "应收账款",

       T3.PREP          AS "预付款项",

       T3.              PREMRECE AS "应收保费",

       T3.              REINRECE AS "应收分保账款",

       T3.              REINCONTRESE AS "应收分保合同准备金",

       T3.              INTERECE AS "应收利息",

       T3.              DIVIDRECE AS "应收股利",

       T3.              OTHERRECE AS "其他应收款",

       T3.              EXPOTAXREBARECE AS "应收出口退税",

       T3.              SUBSRECE AS "应收补贴款",

       T3.              MARGRECE AS "应收保证金",

       T3.              INTELRECE AS "内部应收款",

       T3.              PURCRESAASSET AS "买入返售金融资产",

       T3.              INVE AS "存货",

       T3.              ACCHELDFORS AS "划分为持有待售的资产",

       T3.              PREPEXPE AS "待摊费用",

       T3.              UNSEG AS "待处理流动资产损益",

       T3.              EXPINONCURRASSET AS "一年内到期的非流动资产",

       T3.              OTHERCURRASSE AS "其他流动资产",

       T3.              TOTCURRASSET AS "流动资产合计",

       T3.              LENDANDLOAN AS "发放贷款及垫款",

       T3.              AVAISELLASSE AS "可供出售金融资产",

       T3.              HOLDINVEDUE AS "持有至到期投资",

       T3.              LONGRECE AS "长期应收款",

       T3.              EQUIINVE AS "长期股权投资",

       T3.              OTHERLONGINVE AS "其他长期投资",

       T3.              INVEPROP AS "投资性房地产",

       T3.              FIXEDASSEIMMO AS "固定资产原值",

       T3.              ACCUDEPR AS "累计折旧",

       T3.              FIXEDASSENETW AS "固定资产净值",

       T3.              FIXEDASSEIMPA AS "固定资产减值准备",

       T3.              FIXEDASSENET AS "固定资产净额",

       T3.              CONSPROG AS "在建工程",

       T3.              ENGIMATE AS "工程物资",

       T3.              FIXEDASSECLEA AS "固定资产清理",

       T3.              PRODASSE AS "生产性生物资产",

       T3.              COMASSE AS "公益性生物资产",

       T3.              HYDRASSET AS "油气资产",

       T3.              INTAASSET AS "无形资产",

       T3.              DEVEEXPE AS "开发支出",

       T3.              GOODWILL AS "商誉",

       T3.              LOGPREPEXPE AS "长期待摊费用",

       T3.              TRADSHARTRAD AS "股权分置流通权",

       T3.              DEFETAXASSET AS "递延所得税资产",

       T3.              OTHERNONCASSE AS "其他非流动资产"

  from TQ_FIN_PROBALSHEETNEW t3)

  select COMPCODE , ENDDATE, zhibiao, jine from t4 unpivot (jine for zhibiao in ("应收保费",

"应收分保账款",

"应收分保合同准备金",

"应收利息",

"应收股利",

"其他应收款",

"应收出口退税",

"应收补贴款",

"应收保证金",

"内部应收款",

"买入返售金融资产",

"存货",

"划分为持有待售的资产",

"待摊费用",

"待处理流动资产损益",

"一年内到期的非流动资产",

"其他流动资产",

"流动资产合计",

"发放贷款及垫款",

"可供出售金融资产",

"持有至到期投资"    ,

"长期应收款"          ,

"长期股权投资"        ,

"其他长期投资"    ,

"投资性房地产"        ,

"固定资产原值"    ,

"累计折旧"            ,

"固定资产净值"    ,

"固定资产减值准备",

"固定资产净额"    ,

"在建工程"            ,

"工程物资"            ,

"固定资产清理"    ,

"生产性生物资产"      ,

"公益性生物资产"        ,

"油气资产"            ,

"无形资产"            ,

"开发支出"            ,

"商誉"                ,

"长期待摊费用"      ,

"股权分置流通权"  ,

"递延所得税资产"  ,

"其他非流动资产"

) )