PIVOT, UNPIVOT, MODEL and MATCH_RECOGNIZE

Also: polymorphic table functions (PTF) with DBMS_TF.

16.1 PIVOT and UNPIVOT

SELECT * FROM (SELECT department_id, job_id, salary FROM employees)
PIVOT (SUM(salary) AS total, COUNT(*) AS cnt FOR job_id IN ('IT_PROG' AS it, 'SA_REP' AS sales, 'ST_CLERK' AS clerk));

SELECT * FROM quarterly_sales
UNPIVOT INCLUDE NULLS (amount FOR quarter IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4'));

-- PIVOT XML for dynamic values
SELECT * FROM (SELECT department_id, job_id, salary FROM employees)
PIVOT XML (SUM(salary) FOR job_id IN (ANY));

16.2 MODEL Clause (Spreadsheet-like Calculations)

SELECT country, year, sales
FROM sales_view
MODEL
  PARTITION BY (country) DIMENSION BY (year) MEASURES (sales)
  RULES (sales[2026] = sales[2025] * 1.10,
         sales[2027] = AVG(sales)[year BETWEEN 2023 AND 2025])
ORDER BY country, year;

16.3 MATCH_RECOGNIZE (Row Pattern Matching, 12c)

-- Find V-shaped stock price patterns
SELECT * FROM stock_ticker
MATCH_RECOGNIZE (
  PARTITION BY symbol ORDER BY trade_date
  MEASURES STRT.trade_date AS start_date, LAST(DOWN.trade_date) AS bottom_date, LAST(UP.trade_date) AS end_date
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO LAST UP
  PATTERN (STRT DOWN+ UP+)
  DEFINE DOWN AS DOWN.price < PREV(DOWN.price),
         UP   AS UP.price > PREV(UP.price)
);

16.4 SQL Macros (19.7+ / 21c)

Also: polymorphic table functions (PTF) with DBMS_TF.

CREATE OR REPLACE FUNCTION top_n(t DBMS_TF.TABLE_T, n NUMBER) RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN 'SELECT * FROM t FETCH FIRST n ROWS ONLY';
END;
/
SELECT * FROM top_n(employees, 5);