--- title: "15 — Advanced SQL: CTEs, Recursive Queries, Pivot, and Dynamic SQL" uid: advanced-sql tags: ["sql", "roadmap:sql", "dynamic-sql", "pivot", "advanced", "recursive", "window-functions", "cte", "unpivot"] excerpt: "CTEs name intermediate results for readability and reuse; recursive queries walk hierarchies; pivot reshapes rows to columns; dynamic SQL builds statements at runtime." date: 2026-08-13T03:27:35+0000 source: https://www.aveshina.my.id/en/blog/advanced-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. ```figure ``` ## 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](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](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/](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](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](https://builtin.com/data-science/recursive-sql) [6] Built In, "SQL PIVOT," builtin.com, 2024. [Online]. Available: [https://builtin.com/articles/sql-pivot](https://builtin.com/articles/sql-pivot) [7] DuckDB, "SQL UNPIVOT," duckdb.org, 2024. [Online]. Available: [https://duckdb.org/docs/sql/statements/unpivot.html](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/](https://www.sqlshack.com/dynamic-sql-in-sql-server/) [9] YouTube, "Dynamic SQL," 2023. [Online]. Available: [https://www.youtube.com/watch?v=01LZMCotcpY](https://www.youtube.com/watch?v=01LZMCotcpY) ```quiz Q: What problem does a CTE primarily solve? - It speeds up every query automatically - It names an intermediate result for readability and reuse within a statement correct: 1 explain: A CTE (WITH name AS (...)) gives a subquery a name, so complex queries read top-to-bottom and the same intermediate result can be referenced multiple times. Q: A recursive CTE is the right tool for… - walking hierarchical data where rows reference other rows in the same table (parent_id) - joining two unrelated tables correct: 0 explain: Recursive CTEs refer to themselves, letting a query traverse a tree or hierarchy (org charts, category trees) until a stop condition, without an application-side loop. Q: What does pivoting do? - Rotates distinct row values into columns, aggregating as it goes - Deletes duplicate rows correct: 0 explain: Pivot turns values from one column (e.g., month names) into column headers, producing a wider, summarized result. Unpivot is the reverse. Q: The main risk when building dynamic SQL from user input is… - SQL injection, if raw input is concatenated into the statement string - the query runs too fast correct: 0 explain: Concatenating user input into a SQL string lets an attacker inject clauses. Bind parameters for values, and whitelist any variable identifiers (table/column names can't be parameterized). Q: Which problem signals "reach for a window function"? - I need a running total, moving average, or per-group rank while keeping every row in the output - I need to collapse rows into one summary per group correct: 0 explain: Window functions compute across a frame of related rows but keep each row. Aggregates with GROUP BY collapse rows — that's the opposite of what a window function does. ```