15 — Advanced SQL: CTEs, Recursive Queries, Pivot, and Dynamic SQL

"Tricks for special occasions" was how I thought of the advanced toolkit, which meant I reinvented each one slowly. Writing them down reframed each by its actual job: a CTE names an intermediate result for readability and reuse; a recursive query walks a hierarchy by referring to itself; pivot/unpivot reshapes data between row-form and column-form; and dynamic SQL assembles a statement at runtime from pieces that can't be known in advance. [1][2][3][4]

The framing that clicked is that none of these are tricks — each is a precise answer to a precise structural problem. A query that's hard to read because of nested subqueries needs a CTE. A query over a tree (org chart, category ancestry) needs recursion. A report that wants months as columns needs a pivot. A query whose table or column name depends on a parameter needs dynamic SQL. Match the structural problem to the tool.

Window functions — the bridge

I covered window functions in the transactions/analytics notes, but they belong to this advanced set [1]. The recap: a window function computes over a frame of related rows and keeps every row in the output, enabling running totals, moving averages, and per-group rankings that aggregates and self-joins express clumsily. SUM(x) OVER (...), RANK() OVER (...), LAG(x) OVER (...) — these are the foundation on which the rest of the advanced toolkit builds.

CTEs — name the intermediate result

A Common Table Expression is a named temporary result defined with WITH, scoped to the statement that follows [2][3]. It exists to make complex queries readable and reusable:

WITH big_spenders AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 1000
)
SELECT c.name, b.spent
FROM big_spenders b
JOIN customers c ON c.id = b.customer_id
ORDER BY b.spent DESC;

The payoff is twofold. Readability: instead of a nested subquery buried in a FROM, the intermediate step has a name and reads top-to-bottom. Reuse: I can reference the same CTE multiple times in one statement without re-writing it. When a query starts feeling like a wall of nested parentheses, that's the signal to lift pieces into CTEs.

Recursive queries — walk a hierarchy

A recursive CTE refers to itself, which lets it traverse hierarchical or tree-structured data — org charts, category trees, bill-of-materials [4][5]. It has two parts: a base case (the seed) and a recursive step that builds on the previous iteration.

WITH RECURSIVE org_chain AS (
  -- seed: start at one employee
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE id = 5

  UNION ALL

  -- recursive step: walk up to each manager
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chain oc ON e.id = oc.manager_id
)
SELECT * FROM org_chain;

This walks from employee 5 up through every manager until there's no parent. The same shape walks downward (reports, sub-categories) by flipping the join direction. Before recursive CTEs existed, this required self-joins with a fixed depth limit or an application-side loop; the recursive CTE expresses "keep going until there's no more parent" directly.

Pivot and unpivot — reshape between rows and columns

Pivot rotates rows into columns, aggregating as it goes; unpivot does the reverse, turning columns into rows [6][7]. The classic pivot use is a report that wants months as columns:

raw (rows):                 pivoted (columns):
customer | month | total    customer | Jan | Feb | Mar
A        | Jan   | 100      A        | 100 | 80  | NULL
A        | Feb   | 80       B        | NULL| 60  | 90
B        | Feb   | 60
B        | Mar   | 90

The exact syntax varies by engine (PIVOT in SQL Server, conditional aggregation with SUM(CASE WHEN month='Jan' THEN total END) in standard SQL), but the structural operation is the same: values in one column become column headers. Unpivot is the tool when a wide table (one column per month) needs to become a long one (a month column plus a value column) for further processing.

Dynamic SQL — assemble statements at runtime

Dynamic SQL builds a SQL string at runtime from pieces that can't be known at write time — a table name chosen by a parameter, a WHERE clause with a variable number of filters, a sort column from user input [8][9]:

-- SQL Server spelling
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM ' + @table + ' WHERE active = 1';
EXEC sp_executesql @sql;

The power is flexibility: the statement adapts to inputs that aren't known until the moment of execution. The cost is two-fold. Performance: dynamic SQL often can't be precompiled, and plan caching depends on the exact string. Security: concatenating user input into a SQL string is the textbook path to SQL injection. The mitigation is non-negotiable — never interpolate raw input; parameterize, or whitelist the variable parts (table and column names can't be parameterized, so they must come from a fixed allow-list). I reach for dynamic SQL only when the structure of the query itself varies, and I treat any user-controlled piece as a value to bind, not a string to concatenate.

How I use this

The habit I keep is the structural-problem check. When a query's nesting makes it unreadable, I lift subqueries into CTEs. When the data is a hierarchy (parent pointers in the same table), I write a recursive CTE instead of looping in application code. When a report wants categories as columns, I pivot. And when the query's own structure depends on a parameter, I use dynamic SQL — with every variable piece whitelisted or parameterized. Reaching for these tools by the problem they solve, rather than as flourishes, is what keeps advanced SQL readable instead of clever.

References

[1] Mode Analytics, "SQL Window Functions," mode.com, 2024. [Online]. Available: https://mode.com/sql-tutorial/sql-window-functions

[2] Hightouch, "Common Table Expressions (CTEs)," hightouch.com, 2024. [Online]. Available: https://hightouch.com/sql-dictionary/sql-common-table-expression-cte

[3] LearnSQL, "What is a Common Table Expression?," learnsql.com, 2024. [Online]. Available: https://learnsql.com/blog/what-is-common-table-expression/

[4] Codedamn, "Recursive Queries in SQL," codedamn.com, 2024. [Online]. Available: https://codedamn.com/news/sql/recursive-queries-in-sql

[5] Built In, "Recursive SQL Expression Visually Explained," builtin.com, 2024. [Online]. Available: https://builtin.com/data-science/recursive-sql

[6] Built In, "SQL PIVOT," builtin.com, 2024. [Online]. Available: https://builtin.com/articles/sql-pivot

[7] DuckDB, "SQL UNPIVOT," duckdb.org, 2024. [Online]. Available: https://duckdb.org/docs/sql/statements/unpivot.html

[8] SQLShack, "Dynamic SQL in SQL Server," sqlshack.com, 2024. [Online]. Available: https://www.sqlshack.com/dynamic-sql-in-sql-server/

[9] YouTube, "Dynamic SQL," 2023. [Online]. Available: https://www.youtube.com/watch?v=01LZMCotcpY