Oracle 查询以检索给定年份的按月总金额

你好，

我需要以以下格式检索给定年份的按月总金额-

Org  Jan'18 Feb'18 Mar'18
a $100 $200 $111
b $222 $333 $100

早些时候，我在同一时期获得了行数，但是我不确定如何使用case语句获取总金额？

select org_name
count(case to_char(A.creation_date,'YYYY-MM') when '2018-06' then 1  end)"Jun-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-07' then 1  end)"Jul-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-08' then 1  end)"Aug-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-09' then 1  end)"Sep-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-10' then 1  end)"Oct-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-11' then 1  end)"Nov-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-12' then 1  end)"Dec-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-01' then 1  end)"Jan-19 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-02' then 1  end)"Feb-19 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-03' then 1  end)"Mar-19 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-04' then 1  end)"Apr-19 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-05' then 1  end)"May-19 "
from table1 a,table2 etl
where  a.ORG_ID             = etl.org_id
AND etl.LANGUAGE='US'
and etl.effective_end_date> sysdate
group by etl.org_name

如果要将每个月的总数相加，只需将计数更改为值的总和:

select org_name,
  sum(case to_char(A.creation_date,'YYYY-MM') when '2018-06' then val end)"Jun-18 ",
  sum(case to_char(A.creation_date,'YYYY-MM') when '2018-07' then val end)"Jul-18 ",
  sum(case to_char(A.creation_date,'YYYY-MM') when '2018-08' then val end)"Aug-18 ",
  sum(case to_char(A.creation_date,'YYYY-MM') when '2018-09' then val end)"Sep-18 ",
  ...
from table1 a,table2 etl
where a.ORG_ID = etl.org_id
AND etl.LANGUAGE='US'
and etl.effective_end_date> sysdate
group by etl.org_name;

或者，如果您使用的是现代版本的Oracle数据库，请切换到pivot子句:

with rws as (
  select org_name, to_char(A.creation_date,'YYYY-MM') dt, val
  from   table1 a,table2 etl
  where a.ORG_ID = etl.org_id
  AND   etl.LANGUAGE='US'
  and   etl.effective_end_date> sysdate
)
  select * from rws
  pivot (
    sum ( val ) for dt in (
      '2018-06' "Jun-18", '2018-07' "Jul-18", '2018-08' "Aug-18",
    )
  )

阅读更多关于这方面的信息https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot