oracle中的交叉制表 [英] Cross Tabulation in oracle

问题描述

我的查询如下：

 选择解码（FIN01_NAME，' 收入'，FIN02_NAME）收入，
解码（FIN01_NAME，'  支出'，FIN02_NAME）支出，
解码（FIN01_NAME，' 固定资产'，FIN02_NAME）FixedAssets，
 decode（FIN01_NAME，' 已修复责任，FIN02_NAME）FixedLiability，
 decode（FIN01_NAME，' 当前资产'，FIN02_NAME）CurrentAssests，
 decode（FIN01_NAME，' 当前责任' ，FIN02_NAME）CurrentLiability 来自（
  SELECT  FI N01_name，FIN02_NAME 
  FROM  FIN02_CATEGORY 
  JOIN  
 FIN01_GROUP 
  USING （FIN01_ID） order   by  FIN01_ID ）; 

它输出如，

收入支出固定资产固定资产现金流量
 
捐赠
拨款
商誉
打印机
电脑
家具
 PrinterRepairCost 
升级S / w 
 BankLoan 
 LongTermDepts 
 ShortTermInvesting 
现金
税
 VaccationPay 

但我想要一个输出，如下：

收入支出FIXEDASSETS FIXEDLIABILITY CURRENTASSESTS CURRENTLIABILITY 
 
捐赠商誉打印机银行贷款短期投资税
拨款电脑LongTermDepts现金VaccationPay 
家具
 PrinterRepairCost 
升级S / w 

你能给出任何建议.. ????

谢谢..

ajith。

解决方案

  WITH  CTE  AS （
  SELECT  FIN01_name 
，FIN02_NAME 
，ROW_NUMBER（） OVER （ PARTITION   BY  FIN01_name  ORDER   BY  FIN02_NAME NULLS LAST）rn 
  FROM  FIN02_CATEGORY 
  JOIN  FIN01_GROUP 
  USING  FIN01_ID 
）
  SELECT  * 
  FROM  CTE 
 PIVOT（
 Max（ FIN02_NAME）
  FOR  FIN01_name  IN （' 收入'，' 支出'，' 固定资产'，' 固定责任'，' 当前资产数据 ，' 当前责任'）
）
; 

这可能适用于Oracle 11G R2，在早期版本中不起作用，而且未经测试。

I have a query as below:

select  decode(FIN01_NAME,'Income',FIN02_NAME)income,
        decode(FIN01_NAME,'Expenditure',FIN02_NAME)Expenditure,
        decode(FIN01_NAME,'Fixed Assets',FIN02_NAME)FixedAssets,
        decode(FIN01_NAME,'Fixed Liability',FIN02_NAME)FixedLiability,
        decode(FIN01_NAME,'Current Assests',FIN02_NAME)CurrentAssests,
        decode(FIN01_NAME,'Current Liability',FIN02_NAME)CurrentLiability from (
SELECT   FIN01_name,  FIN02_NAME
  FROM      FIN02_CATEGORY
         JOIN
            FIN01_GROUP
         USING (FIN01_ID) order by FIN01_ID );

It gives an output like,

INCOME	EXPENDITURE FIXEDASSETS	FIXEDLIABILITY	CURRENTASSESTS	CURRENTLIABILITY

Donation					
Grants					
	Goodwill				
		Printer			
		Computer			
		Furniture			
		PrinterRepairCost			
		Upgrading S/w			
			        BankLoan		
			        LongTermDepts		
				              ShortTermInvesting	
				               Cash	
					                         Tax
					                         VaccationPay

but i want an output,as follows:

INCOME  EXPENDITURE FIXEDASSETS FIXEDLIABILITY  CURRENTASSESTS  CURRENTLIABILITY

Donation Goodwill   Printer       BankLoan      ShortTermInvesting  Tax
Grants              Computer      LongTermDepts Cash                VaccationPay
                    Furniture
                    PrinterRepairCost
                    Upgrading S/w

Can you give any suggestion..????
Thanks..
ajith.

解决方案

WITH CTE AS (
    SELECT  FIN01_name
           ,FIN02_NAME
           ,ROW_NUMBER( ) OVER (PARTITION BY FIN01_name ORDER BY FIN02_NAME NULLS LAST) rn
    FROM    FIN02_CATEGORY
    JOIN    FIN01_GROUP
    USING   FIN01_ID
    )
SELECT  *
FROM    CTE
PIVOT   (
    Max(FIN02_NAME)
    FOR FIN01_name IN ('Income','Expenditure','Fixed Assets','Fixed Liability','Current Assests','Current Liability')
    )
;

This could work in Oracle 11G R2, won''t work in earlier versions and it''s untested.

这篇关于oracle中的交叉制表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！