在oracle中使用pivot查询结果 [英] query result with pivot in oracle

问题描述

我有这样的查询

 创建 或将视图 test 替换为 
 < span class =code-keyword>选择 ppo_no，t.upload_month，t.upload_year，t.net_due，dr，com，（ case  当 t.upload_month = '  Mar' 然后  1  
  else  
（ case  当 t.upload_month = '  Apr' 然后  2  
  else  
（ case  何时 t.upload_ month = ' 可以' 然后  3  
  else  
（ case  当 t.upload_month = '  Jun' 然后  4  
  else  
（ case    t.upload_month =  '  7月' 然后  5  
  else  
（ case  当 t.upload_month = '  Aug' 然后  6  
  else  
（ case  < span class =code-keyword>当 t.upload_month = '  Sep' < span class =code-keyword>然后  7  
  else  
（ case    t.upload_month = '  10月' 然后  8  
  else  
（ case  何时 t.upload_month = '  11月' 然后  9  
  else  
（ case   when  t.upload_month = '  Dec' 然后  10  
  else  
（ case    t.upload_month = '  Jan' 然后  11  
  else  
（ case    t.upload_month = < span class =code-string>'  2月' 然后  12  
  else   13  
  end  
） end ） end ） 结束）< span class =code-keyword> end ） end ） end ） end ） end ） end ） end ） end ） as  mm 
 来自 
 t_replicate_detail t 
 其中（t.upload_year> = '  2007' 和 t.upload_year< =   2007'）和（t.upload_month    （'  Jan'，'  2月'））
  UNION  
 选择 ppo_no，upload_month，upload_year，net_due，dr，com，
（
  case  当 upload_month = '  Jan ' 然后  11  
  else  
（ case    upload_month = '  2月' 然后  12  结束）结束）
  as  mm 来自 t_replicate_detail 其中 upload_month  in （'  Jan'，'  2月'）和 upload_year = '  2008' 
  order  
  by  upload_year，ppo_no，mm; 

--------- -----------------------------------------



可以转换此结果，例如



P.Detail / Mth。 MAR APR 5月5月7月7月6月6月12月1月CPB总计



B.PENSION

DR

COMM 。

医疗

ARREAR

扣款

TDS

RECOVERY



毛收入（不扣除）

净收入

解决方案

能够得到每一个月份作为列数据，请使用以下内容：

  SELECT  *，[Mar] + [Apr ] + ... [Feb]  AS 总计
  FROM （
  SELECT  t.upload_year，SUM（ CASE   WHEN  t .upload_month = '  Mar'  AND  t .upload_year = '  2007' 那么 t .net_due  ELSE   NULL   END ） AS  [Mar]，
 SUM（ CASE   WHEN  t .upload_month = '  Apr'  AND  t .upload_year = '  2007' 那么 t .net_due  ELSE   NULL   END ） AS  [Apr]，
 SUM（ CASE   WHEN  t.upload_month = ' 可能'  AND  t.upload_year = '  2007'  THEN  t.net_due  ELSE   NULL  结束） AS  [May]，
 ... 
 SUM（ CASE   WHEN  t.upload_month = '  Jan'  AND  t.upload_year = '  2008'  THEN  t.net_due  ELSE   NULL   END ） AS  [Jan]，
 SUM（ CASE   WHEN  t.upload_month = '  Feb'  AND  t.upload_year = '  2008' 那么 t.net_due  ELSE   NULL   END ） AS  [Feb] 
  FROM  ... 
  WHERE  t.upload_year  IN （'  2007'，'  2008'）
  GROUP   BY  t.upload_year 
） AS  FinancialYearData 

以上示例显示如何获得每月 net_due 的总和。



我是建议阅读： Oracle数据库中的PIVOT和UNPIVOT运算符11g第1版 [ ^ ]



注意：我不知道为什么要将数字数据存储为字符串（ upload_year ）！

i have a query like this

create or replace view test as
select ppo_no,t.upload_month,t.upload_year,t.net_due ,dr,com,(case when t.upload_month ='Mar' then 1
else
(case when t.upload_month ='Apr' then 2
else
(case when t.upload_month ='May' then 3
else
(case when t.upload_month ='Jun' then 4
else
(case when t.upload_month ='Jul' then 5
else
(case when t.upload_month ='Aug' then 6
else
(case when t.upload_month ='Sep' then 7
else
(case when t.upload_month ='Oct' then 8
else
(case when t.upload_month ='Nov' then 9
else
(case when t.upload_month ='Dec' then 10
else
(case when t.upload_month ='Jan' then 11
else
(case when t.upload_month ='Feb' then 12
else 13
end
) end)end)end)end)end)end)end)end)end)end)end) as mm
from
t_replicate_detail t
where  (t.upload_year >= '2007' and t.upload_year <='2007') and (t.upload_month not in ('Jan','Feb'))
UNION
select ppo_no,upload_month,upload_year, net_due,dr,com,
(
case when upload_month ='Jan' then 11
else
(case when upload_month ='Feb' then 12 end)end)
as mm from t_replicate_detail where   upload_month in('Jan','Feb') and upload_year = '2008'
order
by upload_year,ppo_no,mm;

--------------------------------------------------

could any any convert this result like

P.Detail/Mth. MAR APR MAY JUNE JULY AUG SEP OCT NOV DEC JAN FEB Total

B.PENSION
DR
COMM.
MEDICAL
ARREAR
DEDUCTIONS
TDS
RECOVERY

Gross Income (without deductions)
Net Income

解决方案

To be able to get each month as a column data, use something like this:

SELECT *, [Mar] + [Apr] + ... [Feb] AS Total
FROM (
    SELECT t.upload_year, SUM(CASE WHEN t.upload_month ='Mar' AND t.upload_year = '2007' THEN t.net_due ELSE NULL END) AS [Mar],
        SUM(CASE WHEN t.upload_month ='Apr' AND t.upload_year = '2007'  THEN t.net_due ELSE NULL END) AS [Apr],
        SUM(CASE WHEN t.upload_month ='May' AND t.upload_year = '2007'  THEN t.net_due ELSE NULL END) AS [May],
        ...
        SUM(CASE WHEN t.upload_month ='Jan' AND t.upload_year = '2008' THEN t.net_due ELSE NULL END) AS [Jan],
        SUM(CASE WHEN t.upload_month ='Feb' AND t.upload_year = '2008' THEN t.net_due ELSE NULL END) AS [Feb]
    FROM ...
    WHERE t.upload_year IN ('2007','2008')
    GROUP BY t.upload_year
) AS FinancialYearData

Above example shows how to get sum of net_due for each month.

I'd suggest to read this: PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1[^]

Note: i have no idea why do you store numeric data as a string (upload_year)!

这篇关于在oracle中使用pivot查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！