PIVOT/UNPIVOT, GROUPING SETS, CUBE, ROLLUP, and Advanced Aggregation

Learning outcomes

ServiceHub has row-oriented monthly revenue but reporting consumers want quarter columns, subtotal rows, and a grand total. A naive solution runs many separate queries and labels every null as “TOTAL,” accidentally merging genuine unknown dimensions with generated subtotal nulls. Oracle’s reshaping and grouping extensions can express the result in one statement—if their semantics are understood.

1. PIVOT performs aggregation plus rotation

PIVOT implicitly groups by source columns not referenced by the pivot clause, applies the requested aggregate, and emits columns for values listed in the IN list. Use a narrow source subquery so accidental extra columns do not silently become grouping keys.

SELECT *FROM (  SELECT region_code, quarter_code, amount  FROM servicehub_sql_revenue)PIVOT (  SUM(amount) AS revenue  FOR quarter_code IN (    'Q1' AS q1,    'Q2' AS q2,    'Q3' AS q3,    'Q4' AS q4  ))ORDER BY region_code;

Static non-XML PIVOT requires known pivot values. If categories are truly dynamic, decide whether the consumer should receive rows, generated SQL under strict identifier validation, or the XML form; do not concatenate untrusted labels into SQL text.

2. UNPIVOT rotates columns to rows—and excludes NULLs by default

SELECT region_code, quarter_code, amountFROM servicehub_sql_quarter_wideUNPIVOT (  amount FOR quarter_code IN (    q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4'  ))ORDER BY region_code, quarter_code;SELECT region_code, quarter_code, amountFROM servicehub_sql_quarter_wideUNPIVOT INCLUDE NULLS (  amount FOR quarter_code IN (    q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4'  ))ORDER BY region_code, quarter_code;

The first query omits null-valued source cells. That is data-shaping behavior, not proof that those columns never existed. Also ensure unpivoted value columns are in a compatible data-type group.

3. GROUPING SETS asks only for the subtotal levels you need

SELECT region_code,       service_type,       SUM(amount) AS amount,       GROUPING(region_code) AS g_region,       GROUPING(service_type) AS g_type,       GROUPING_ID(region_code, service_type) AS grouping_idFROM servicehub_sql_revenueGROUP BY GROUPING SETS (  (region_code, service_type),  (region_code),  ())ORDER BY GROUPING_ID(region_code, service_type), region_code, service_type;

GROUPING SETS is often clearer than computing a full cube when only selected subtotal levels matter. Oracle conceptually combines the requested grouping results, and duplicate-looking rows can be legitimate if grouping sets overlap.

4. Deliberately wrong: NVL cannot distinguish a real NULL from a subtotal NULL

SELECT NVL(region_code, 'ALL REGIONS') AS region_label,       SUM(amount) AS amountFROM servicehub_sql_revenueGROUP BY ROLLUP(region_code);

If a stored row legitimately has region_code IS NULL, this labels it exactly like the generated grand-total row. Use GROUPING(region_code), which returns 1 for a null generated by a grouping extension and 0 for a stored null.

SELECT CASE         WHEN GROUPING(region_code) = 1 THEN 'ALL REGIONS'         WHEN region_code IS NULL THEN 'UNKNOWN REGION'         ELSE region_code       END AS region_label,       SUM(amount) AS amountFROM servicehub_sql_revenueGROUP BY ROLLUP(region_code)ORDER BY GROUPING(region_code), region_code;

5. ROLLUP and CUBE have different combinatorics

ROLLUP(a,b,c) generates a hierarchy of grouping prefixes: detail, then progressively higher subtotals, then grand total. CUBE(a,b,c) generates every combination of those dimensions. That can be exactly right for multidimensional analysis—or excessive work and output if the consumer needs only a few levels.

SELECT region_code, service_type, SUM(amount) AS amountFROM servicehub_sql_revenueGROUP BY ROLLUP(region_code, service_type);SELECT region_code, service_type, SUM(amount) AS amountFROM servicehub_sql_revenueGROUP BY CUBE(region_code, service_type);

Do not choose CUBE because it sounds more advanced. Choose it because every dimension combination has a defined consumer.

6. Conditional aggregation can be clearer than PIVOT

SELECT region_code,       SUM(CASE WHEN quarter_code='Q1' THEN amount END) AS q1,       SUM(CASE WHEN quarter_code='Q2' THEN amount END) AS q2,       SUM(CASE WHEN quarter_code='Q3' THEN amount END) AS q3,       SUM(CASE WHEN quarter_code='Q4' THEN amount END) AS q4FROM servicehub_sql_revenueGROUP BY region_codeORDER BY region_code;

Compare readability, output-column contracts, and measured plans. A specialized operator is not automatically faster than an equivalent simpler formulation.

7. Hands-on lab

DROP TABLE servicehub_sql_quarter_wide IF EXISTS PURGE;DROP TABLE servicehub_sql_revenue IF EXISTS PURGE;CREATE TABLE servicehub_sql_revenue (  region_code VARCHAR2(20),  service_type VARCHAR2(20) NOT NULL,  quarter_code VARCHAR2(2) NOT NULL,  amount NUMBER(12,2) NOT NULL);INSERT INTO servicehub_sql_revenue VALUES ('NORTH','INSTALL','Q1',1000);INSERT INTO servicehub_sql_revenue VALUES ('NORTH','REPAIR','Q1',600);INSERT INTO servicehub_sql_revenue VALUES ('NORTH','INSTALL','Q2',1200);INSERT INTO servicehub_sql_revenue VALUES ('SOUTH','REPAIR','Q1',700);INSERT INTO servicehub_sql_revenue VALUES (NULL,'REPAIR','Q2',150);CREATE TABLE servicehub_sql_quarter_wide (  region_code VARCHAR2(20), q1 NUMBER, q2 NUMBER, q3 NUMBER, q4 NUMBER);INSERT INTO servicehub_sql_quarter_wide VALUES ('NORTH',1600,1200,NULL,NULL);INSERT INTO servicehub_sql_quarter_wide VALUES ('SOUTH',700,NULL,NULL,NULL);COMMIT;

EXPLAIN PLAN SET STATEMENT_ID='SH06L2'FORSELECT region_code, service_type, SUM(amount)FROM servicehub_sql_revenueGROUP BY GROUPING SETS ((region_code,service_type),(region_code),());SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH06L2','BASIC'));-- Estimated-plan evidence only; exact operators are not a semantic contract.

DROP TABLE servicehub_sql_quarter_wide PURGE;DROP TABLE servicehub_sql_revenue PURGE;

8. Production judgment

Use PIVOT when a stable cross-tab shape improves clarity; use rows or conditional aggregation when it does not. Use GROUPING SETS to request explicit subtotal levels, ROLLUP for natural hierarchies, and CUBE only when all combinations are valuable. Generated subtotal nulls must be identified with GROUPING/GROUPING_ID, not text substitution. No paid pack, restart, or COMPATIBLE change is required for the mandatory examples.

Authoritative references

• SELECT — PIVOT/UNPIVOT — pivot implicit grouping and unpivot null/type behavior
• SELECT — GROUP BY extensions — ROLLUP, CUBE and GROUPING SETS semantics
• SQL for Analysis and Reporting — warehouse-oriented pivot and aggregation guidance