Oracle – How to Pivot, Categorize, and Group By

group byoracleoracle-12cpivot

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.

Best Answer

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.)}

Related Solutions

Mysql – Pivot table with MySQL

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;


SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)

Using PIVOT for a table in ORACLE 12C

-- sample data
with data as
(
  select 34 as id, 1 as an_id, 'FIRST NAME' as title, 
    'ANDY' as full_title from dual union all
  select 34 as id, 1 as an_id, 'LAST NAME' as title, 
    'COOL' as full_title from dual union all
  select 34 as id, 1 as an_id, 'FIRST & LAST' as title, 
    'ANDY COOL' as full_title from dual union all
  select 34 as id, 2 as an_id, 'FIRST NAME' as title, 
    'SCHUMACK' as full_title from dual union all
  select 34 as id, 2 as an_id, 'LAST NAME' as title, 
    'LAST' as full_title from dual union all
  select 34 as id, 2 as an_id, 'FIRST & LAST' as title, 
    'SCHUMACK LAST' as full_title from dual union all
  select 32 as id, 1 as an_id, 'FIRST NAME' as title, 
    'MARIO' as full_title from dual union all
  select 32 as id, 1 as an_id, 'LAST NAME' as title, 
    'SANTI' as full_title from dual union all
  select 32 as id, 1 as an_id, 'FIRST & LAST' as title, 
    'MARIO SANTI' as full_title from dual
)
-- query
select * from data
pivot (min(full_title) for title in ('FIRST NAME', 'LAST NAME', 'FIRST & LAST'))
order by id desc, an_id
;


        ID      AN_ID 'FIRST NAME'  'LAST NAME'   'FIRST & LAST
---------- ---------- ------------- ------------- -------------
        34          1 ANDY          COOL          ANDY COOL    
        34          2 SCHUMACK      LAST          SCHUMACK LAST
        32          1 MARIO         SANTI         MARIO SANTI

Note that PIVOT requires an aggregate function, I could have used for example MAX as well.

Best Answer

Related Solutions

Mysql – Pivot table with MySQL

Using PIVOT for a table in ORACLE 12C

Related Question