Pivot, categorize and group by

Question

This question is similar, yet different, to another DBA SE question I have here: Group by NAME and YEAR, categorize NAME

---

I have a FINANCE_SOURCE table:

+-----------+------------+-------------+--------+------+
| SOURCE_ID | PROJECT_ID | SOURCE_NAME | AMOUNT | YEAR |
+-----------+------------+-------------+--------+------+
|       101 |          1 | A           |  10.00 | 2017 |
|       102 |          1 | B           |   5.00 | 2017 |
|       103 |          1 | B           |  15.00 | 2017 |
|       104 |          1 | B           |  70.00 | 2016 |
|       105 |          1 | C           |  30.00 | 2017 |
|       106 |          1 | D           |   1.00 | 2016 |
|       107 |          1 | D           |  20.00 | 2017 |
+-----------+------------+-------------+--------+------+

I want to rearrange the data, so that SOURCE_A, SOURCE_B, and OTHER(anything that's not A or B) are in separate columns.

The data needs to be grouped into separate YEARS:

+------------+---------+---------+-------------+-------+------+
| PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR |
+------------+---------+---------+-------------+-------+------+
|          1 |      10 |      20 |          50 |    80 | 2017 |
|          1 |         |      70 |           1 |    71 | 2016 |
+------------+---------+---------+-------------+-------+------+

How can I do this? Performance is important.

Answer 1

You can get it by using SUM(CASE...) statement.

SELECT   PROJECT_ID,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME NOT IN ('A','B') THEN AMOUNT END), 0) OTHER_TOTAL,
         COALESCE(SUM(AMOUNT), 0) AS TOTAL,
         YEAR
FROM     FINANCE_SOURCE
GROUP BY PROJECT_ID, YEAR;

PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR
---------: | ------: | ------: | ----------: | ----: | ---:
         1 |       0 |      70 |           1 |    71 | 2016
         1 |      10 |      20 |          50 |    80 | 2017

dbfiddle here

As @JackDouglas has suggested, let me add his new solution that could be slightly quicker.

SELECT PROJECT_ID
     , A_TOTAL
     , B_TOTAL
     , nvl(TOTAL,0)-nvl(A_TOTAL,0)-nvl(B_TOTAL,0) OTHER_TOTAL
     , TOTAL
     , YEAR
FROM( SELECT   PROJECT_ID,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
               COALESCE(SUM(AMOUNT), 0) AS TOTAL,
               YEAR
      FROM     FINANCE_SOURCE
      GROUP BY PROJECT_ID, YEAR );

dbfiddle here
_{(Execution plan included.)}

Answer 2

Apart from the traditional SUM(CASE), there are two other ways of getting this sort of PIVOT output.

The first is using the PIVOT and UNPIVOT operators introduced in 11.1:

select project_id
     , a_total
     , b_total
     , other_total
     , nvl(a_total,0)+nvl(b_total,0)+nvl(other_total,0) total
     , year
from ( select project_id
            , decode(source_name,'A','A','B','B','O') src
            , amount
            , year
       from finance_source )
pivot (sum(amount) for src in ('A' a_total, 'B' b_total, 'O' other_total));

PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR
---------: | ------: | ------: | ----------: | ----: | ---:
         1 |    null |      70 |           1 |    71 | 2016
         1 |      10 |      20 |          50 |    80 | 2017

The second is using the MODEL clause introduced in 10.1:

select project_id, a_total, b_total, other_total, total, year
from finance_source
model unique single reference
return updated rows
partition by (project_id, year)
dimension by (source_name)
measures (amount, 0 a_total, 0 b_total, 0 other_total, 0 total)
rules upsert
(
  a_total[1] = sum(amount)['A']
, b_total[1] = sum(amount)['B']
, other_total[1] = nvl(sum(amount)[any],0)-nvl(sum(amount)['B'],0)-nvl(sum(amount)['A'],0)
, total[1] = sum(amount)[any]
);

PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR
---------: | ------: | ------: | ----------: | ----: | ---:
         1 |    null |      70 |           1 |    71 | 2016
         1 |      10 |      20 |          50 |    80 | 2017

In most simple cases I would expect the PIVOT to be faster (and about the same as the SUM(CASE)), but if your real requirements are more complex, you may find the MODEL clause a better fit — it covers a lot of more complex situations too.

dbfiddle here

Answer 3

This isn't quite what you want, but you can always use GROUPING SETS too. I used PostgreSQL for the demo but it should work the same w/ Oracle.

Sample data

CREATE TABLE foo
AS 
  SELECT *
  FROM ( VALUES
    ( 101, 1, 'A', 10.00, 2017 ),
    ( 102, 1, 'B',  5.00, 2017 ),
    ( 103, 1, 'B', 15.00, 2017 ),
    ( 104, 1, 'B', 70.00, 2016 ),
    ( 105, 1, 'C', 30.00, 2017 ),
    ( 106, 1, 'D',  1.00, 2016 ),
    ( 107, 1, 'D', 20.00, 2017 )
  ) AS t(source_id, project_id, source_name, amount, year);

GROUPING SETS

SELECT
  project_id,
  year,
  CASE
    WHEN source_name = 'A' THEN 'A'
    WHEN source_name = 'B' THEN 'B'
    ELSE 'OTHER'
  END AS source,
  sum(amount)
FROM foo
GROUP BY GROUPING SETS(
  (
    project_id,
    year,
    CASE
      WHEN source_name = 'A' THEN 'A'
      WHEN source_name = 'B' THEN 'B'
      ELSE 'OTHER' END
  ),
  (project_id, year)
)
ORDER BY 1, 2, 3;


 project_id | year | source |  sum  
------------+------+--------+-------
          1 | 2016 | B      | 70.00
          1 | 2016 | OTHER  |  1.00
          1 | 2016 |        | 71.00
          1 | 2017 | A      | 10.00
          1 | 2017 | B      | 20.00
          1 | 2017 | OTHER  | 50.00
          1 | 2017 |        | 80.00
(7 rows)