--- # === IDENTITY === id: software/patterns/sql-pivot-unpivot/2026 canonical_question: "How do I write SQL pivot and unpivot queries?" aliases: - "SQL pivot rows to columns" - "SQL unpivot columns to rows" - "SQL Server PIVOT UNPIVOT" - "PostgreSQL crosstab pivot" - "MySQL conditional aggregation pivot" - "Oracle PIVOT UNPIVOT clause" - "SQL transpose rows columns" - "CASE WHEN pivot SQL" - "SQL FILTER clause pivot" - "LATERAL VALUES unpivot" entity_type: software_reference domain: software > patterns > sql_pivot_unpivot region: global jurisdiction: global temporal_scope: 2015-2026 # === VERIFICATION === last_verified: 2026-02-23 confidence: 0.94 version: 1.0 first_published: 2026-02-23 # === TEMPORAL VALIDITY === temporal_validity: status: stable last_breaking_change: "Oracle 11g (2007) and SQL Server 2005 introduced native PIVOT/UNPIVOT; PostgreSQL tablefunc stable since 8.3" next_review: 2026-08-22 change_sensitivity: low # === CONSTRAINTS === constraints: - "PostgreSQL crosstab requires CREATE EXTENSION tablefunc (one-time per database)" - "SQL Server and Oracle PIVOT require column values known at query-write time (static pivot); dynamic pivot needs dynamic SQL" - "MySQL has no native PIVOT/UNPIVOT clause — use CASE+GROUP BY or prepared statements" - "UNPIVOT in SQL Server silently drops NULL values unless you ISNULL/COALESCE first" - "Oracle UNPIVOT requires INCLUDE NULLS to preserve NULL rows" # === SKIP CONDITIONS === skip_this_unit_if: - condition: "Need to transpose an entire result set (all columns become rows, all rows become columns)" use_instead: "Full matrix transpose — handle in application code or use language-specific DataFrame.transpose()" - condition: "Building a dynamic reporting dashboard with unknown column counts" use_instead: "Return normalized rows and pivot in the presentation layer (pandas, Excel, BI tool)" # === AGENT HINTS === inputs_needed: - key: "database_engine" question: "Which database engine are you using?" type: choice options: ["PostgreSQL", "SQL Server", "MySQL/MariaDB", "Oracle", "SQLite", "Other"] - key: "pivot_direction" question: "Do you need to pivot (rows to columns) or unpivot (columns to rows)?" type: choice options: ["pivot", "unpivot", "both"] - key: "columns_known" question: "Are the pivot column values known at query-write time, or dynamic?" type: choice options: ["static (known values)", "dynamic (unknown/changing values)"] # === DISTRIBUTION === canonical_source: "https://knowledgelib.io/software/patterns/sql-pivot-unpivot/2026" suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-23)" # === RELATED UNITS === related_kos: related_to: - id: "software/patterns/sql-window-functions/2026" label: "SQL Window Functions (running totals, rankings, lag/lead)" - id: "software/patterns/sql-recursive-cte/2026" label: "Recursive CTE Queries for Hierarchies" - id: "software/patterns/sql-json-queries/2026" label: "Querying JSON Data in PostgreSQL and MySQL" solves: - id: "software/debugging/postgresql-slow-queries/2026" label: "PostgreSQL Slow Queries" often_confused_with: - id: "software/patterns/sql-dynamic-sql/2026" label: "Dynamic SQL — needed for dynamic pivot but a separate pattern with its own security considerations" # === SOURCES (7 authoritative sources) === sources: - id: src1 title: "PostgreSQL Documentation: tablefunc — crosstab functions" author: PostgreSQL Global Development Group url: https://www.postgresql.org/docs/current/tablefunc.html type: official_docs published: 2025-11-21 reliability: authoritative - id: src2 title: "Using PIVOT and UNPIVOT — SQL Server" author: Microsoft url: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot type: official_docs published: 2024-08-15 reliability: authoritative - id: src3 title: "SQL Pivot in All Databases" author: Markus Winand url: https://modern-sql.com/use-case/pivot type: technical_blog published: 2023-06-01 reliability: high - id: src4 title: "PIVOT and UNPIVOT Operators in Oracle Database 11g" author: Tim Hall url: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1 type: technical_blog published: 2023-10-15 reliability: high - id: src5 title: "Unpivot with Postgres — LATERAL VALUES approach" author: Thomas Kellerer url: https://blog.sql-workbench.eu/post/unpivot-with-postgres/ type: technical_blog published: 2023-08-20 reliability: high - id: src6 title: "The FILTER Clause — Modern SQL" author: Markus Winand url: https://modern-sql.com/feature/filter type: technical_blog published: 2023-04-10 reliability: high - id: src7 title: "Implementing Pivot and Dynamic Pivot Using GROUP_CONCAT in MySQL" author: Perficient url: https://blogs.perficient.com/2024/01/04/implementing-pivot-and-dynamic-pivot-using-group_concat-in-mysql/ type: technical_blog published: 2024-01-04 reliability: moderate_high --- # SQL Pivot and Unpivot Queries ## TL;DR - **Bottom line**: Pivot (rows to columns) and unpivot (columns to rows) are solved differently on every major database engine; pick the right pattern for your platform. - **Key tool/command**: `SELECT ... PIVOT (AGG(val) FOR col IN (...))` on SQL Server/Oracle; `crosstab()` on PostgreSQL; `SUM(CASE WHEN ...)` everywhere. - **Watch out for**: SQL Server UNPIVOT silently drops NULLs — wrap columns in ISNULL() first. - **Works with**: PostgreSQL 8.3+, SQL Server 2005+, Oracle 11g+, MySQL 5.0+ (CASE-based), all versions of MariaDB and SQLite. ## Constraints - PostgreSQL `crosstab()` requires `CREATE EXTENSION tablefunc` — fails with "function crosstab does not exist" without it - Native PIVOT/UNPIVOT (SQL Server, Oracle) only works with a fixed, compile-time list of column values — dynamic columns require dynamic SQL - MySQL and SQLite have no PIVOT/UNPIVOT syntax — use conditional aggregation (`CASE`/`IF` + `GROUP BY`) instead - SQL Server UNPIVOT silently discards rows where the source column is NULL — always use `ISNULL(col, 0)` or `COALESCE` before unpivoting - Oracle UNPIVOT defaults to EXCLUDE NULLS — add `INCLUDE NULLS` if you need to preserve them - PostgreSQL `crosstab()` requires input sorted by the row-name column — unsorted input produces incorrect or missing values ## Quick Reference | Scenario | Pattern | Database | Complexity | Trade-off | |---|---|---|---|---| | Static pivot, known columns | `PIVOT` clause | SQL Server, Oracle | Low | Cleanest syntax, no extension needed | | Static pivot, PostgreSQL | `crosstab()` | PostgreSQL | Medium | Requires tablefunc extension; input must be sorted | | Static pivot, any database | `SUM(CASE WHEN ... END)` | All | Low | Verbose but universally portable | | Static pivot, PostgreSQL 9.4+ | `AGG() FILTER (WHERE ...)` | PostgreSQL | Low | ~8% faster than CASE; cleaner syntax [src6] | | Dynamic pivot, SQL Server | Dynamic SQL + `PIVOT` | SQL Server | High | SQL injection risk; must whitelist values | | Dynamic pivot, MySQL | `GROUP_CONCAT` + `PREPARE` | MySQL | High | Requires prepared statements; max length limit | | Dynamic pivot, PostgreSQL | `crosstab` + dynamic SQL / `plpgsql` | PostgreSQL | High | Function wrapper needed; harder to debug | | Static unpivot, SQL Server/Oracle | `UNPIVOT` clause | SQL Server, Oracle | Low | Clean syntax; watch for NULL handling | | Static unpivot, PostgreSQL | `CROSS JOIN LATERAL (VALUES ...)` | PostgreSQL | Low | Standard SQL; no extension needed [src5] | | Static unpivot, any database | `UNION ALL` per column | All | Low | Verbose; full table scan per UNION branch | | Unpivot with NULLs, SQL Server | `CROSS APPLY (VALUES ...)` | SQL Server | Medium | Preserves NULLs unlike UNPIVOT | | Unpivot with NULLs, Oracle | `UNPIVOT INCLUDE NULLS` | Oracle | Low | Simple flag, often forgotten | | Dynamic unpivot, PostgreSQL | `jsonb_each_text(to_jsonb(row))` | PostgreSQL 9.4+ | Medium | Elegant but slower on large datasets | ## Decision Tree > Full script: [decision-tree.txt](scripts/decision-tree.txt) (25 lines) ``` START ├── Direction: PIVOT (rows → columns)? │ ├── YES → Column values known at query-write time? │ │ ├── YES (static pivot) → Which database? │ │ │ ├── SQL Server / Oracle → Use native PIVOT clause # ... (see full script) ``` ## Step-by-Step Guide ### 1. Identify pivot direction and column values Determine whether you need pivot (rows to columns) or unpivot (columns to rows). List all distinct values that will become column headers. [src3] ```sql -- Find distinct values that will become columns SELECT DISTINCT category FROM sales ORDER BY category; -- Result: 'Electronics', 'Clothing', 'Food' ``` **Verify**: Count the distinct values — this is your output column count. If > 20, consider a different approach. ### 2. Write the pivot query for your database engine Choose the pattern from the Quick Reference table matching your database and static/dynamic requirement. [src1] [src2] ```sql -- PostgreSQL: FILTER approach (recommended for PostgreSQL 9.4+) SELECT sale_year, SUM(amount) FILTER (WHERE category = 'Electronics') AS electronics, SUM(amount) FILTER (WHERE category = 'Clothing') AS clothing, SUM(amount) FILTER (WHERE category = 'Food') AS food FROM sales GROUP BY sale_year ORDER BY sale_year; ``` **Verify**: `SELECT COUNT(DISTINCT category) FROM sales;` → should match the number of pivoted columns ### 3. Handle NULLs and missing combinations Pivot results will have NULLs where no data exists for a row/column combination. Decide whether to show NULL or a default. [src2] ```sql -- Wrap in COALESCE for zero-fill SELECT sale_year, COALESCE(SUM(amount) FILTER (WHERE category = 'Electronics'), 0) AS electronics, COALESCE(SUM(amount) FILTER (WHERE category = 'Clothing'), 0) AS clothing, COALESCE(SUM(amount) FILTER (WHERE category = 'Food'), 0) AS food FROM sales GROUP BY sale_year; ``` **Verify**: Check for unexpected NULLs — `SELECT * FROM WHERE electronics IS NULL;` ### 4. Test with edge cases Verify the query with: (a) a category with no data, (b) NULL values in the pivot column, (c) duplicate rows. [src4] ```sql -- Insert test edge case: NULL category INSERT INTO sales (sale_year, category, amount) VALUES (2025, NULL, 100); -- Re-run pivot — NULL category should not corrupt other columns ``` **Verify**: Row count of pivoted result should equal `COUNT(DISTINCT row_identifier)` from the source. ## Code Examples ### PostgreSQL: Static Pivot with crosstab() ```sql -- Input: sales table with (sale_year, quarter, revenue) rows -- Output: one row per year, columns for Q1-Q4 -- Step 1: Enable extension (once per database) CREATE EXTENSION IF NOT EXISTS tablefunc; -- Step 2: Pivot using crosstab -- IMPORTANT: source query MUST be sorted by the row-name column (sale_year) SELECT * FROM crosstab( $$SELECT sale_year, quarter, SUM(revenue) FROM sales GROUP BY sale_year, quarter ORDER BY sale_year, quarter$$, -- sorted by row-name $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$ ) AS ct(sale_year INT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC); ``` ### PostgreSQL: Static Pivot with FILTER (preferred, no extension) ```sql -- Input: sales table with (region, product, revenue) -- Output: one row per region, one column per product category SELECT region, SUM(revenue) FILTER (WHERE product = 'Widget') AS widget_rev, SUM(revenue) FILTER (WHERE product = 'Gadget') AS gadget_rev, SUM(revenue) FILTER (WHERE product = 'Gizmo') AS gizmo_rev, COUNT(*) FILTER (WHERE product = 'Widget') AS widget_cnt FROM sales GROUP BY region ORDER BY region; -- FILTER is ~8% faster than CASE on PostgreSQL 15+ benchmarks ``` ### SQL Server: PIVOT and UNPIVOT ```sql -- PIVOT: rows to columns -- Input: orders(customer_id, quarter, total_amount) -- Output: one row per customer, columns for each quarter SELECT customer_id, [Q1], [Q2], [Q3], [Q4] FROM ( SELECT customer_id, quarter, total_amount FROM orders ) AS src PIVOT ( SUM(total_amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4]) ) AS pvt ORDER BY customer_id; -- UNPIVOT: columns back to rows -- Input: quarterly_report(customer_id, Q1, Q2, Q3, Q4) -- Output: one row per customer per quarter SELECT customer_id, quarter, total_amount FROM quarterly_report UNPIVOT ( total_amount FOR quarter IN ([Q1], [Q2], [Q3], [Q4]) ) AS unpvt; -- WARNING: UNPIVOT drops rows where value is NULL ``` ### MySQL: CASE-Based Pivot (no native PIVOT) ```sql -- Input: exam_scores(student_id, subject, score) -- Output: one row per student, columns for each subject SELECT student_id, MAX(CASE WHEN subject = 'Math' THEN score END) AS math, MAX(CASE WHEN subject = 'Science' THEN score END) AS science, MAX(CASE WHEN subject = 'English' THEN score END) AS english, MAX(CASE WHEN subject = 'History' THEN score END) AS history FROM exam_scores GROUP BY student_id ORDER BY student_id; -- Use MAX for single values, SUM for aggregation -- Use IF(subject='Math', score, NULL) as shorthand in MySQL ``` ### SQL Server: Dynamic Pivot with sp_executesql ```sql -- Input: product_sales(region, product_name, revenue) -- Output: dynamic columns based on distinct product_name values DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); -- Build column list from data (whitelist if user-supplied!) SELECT @columns = STRING_AGG(QUOTENAME(product_name), ', ') FROM (SELECT DISTINCT product_name FROM product_sales) AS products; SET @sql = N' SELECT region, ' + @columns + N' FROM (SELECT region, product_name, revenue FROM product_sales) AS src PIVOT (SUM(revenue) FOR product_name IN (' + @columns + N')) AS pvt ORDER BY region;'; EXEC sp_executesql @sql; -- QUOTENAME prevents SQL injection for identifier names ``` ### PostgreSQL: Unpivot with LATERAL VALUES ```sql -- Input: quarterly_report(customer_id, q1, q2, q3, q4) -- Output: one row per customer per quarter (preserves NULLs) SELECT r.customer_id, u.quarter, u.amount FROM quarterly_report r CROSS JOIN LATERAL ( VALUES ('Q1', r.q1), ('Q2', r.q2), ('Q3', r.q3), ('Q4', r.q4) ) AS u(quarter, amount) ORDER BY r.customer_id, u.quarter; -- Unlike SQL Server UNPIVOT, this preserves NULL values -- No extension needed — standard SQL ``` ### Oracle: PIVOT and UNPIVOT ```sql -- PIVOT SELECT * FROM ( SELECT department, job_title, salary FROM employees ) PIVOT ( AVG(salary) FOR job_title IN ('Manager' AS mgr, 'Engineer' AS eng, 'Analyst' AS anl) ) ORDER BY department; -- UNPIVOT (preserving NULLs) SELECT department, job_title, avg_salary FROM pivoted_employees UNPIVOT INCLUDE NULLS ( avg_salary FOR job_title IN (mgr AS 'Manager', eng AS 'Engineer', anl AS 'Analyst') ); ``` ## Anti-Patterns ### Wrong: Using UNPIVOT when source columns contain NULLs (SQL Server) ```sql -- BAD — silently drops rows where Q2 or Q3 is NULL SELECT customer_id, quarter, amount FROM quarterly_report UNPIVOT (amount FOR quarter IN (Q1, Q2, Q3, Q4)) AS u; ``` ### Correct: Use CROSS APPLY VALUES to preserve NULLs ```sql -- GOOD — preserves all rows including NULLs SELECT customer_id, quarter, amount FROM quarterly_report CROSS APPLY ( VALUES ('Q1', Q1), ('Q2', Q2), ('Q3', Q3), ('Q4', Q4) ) AS u(quarter, amount); ``` ### Wrong: Unsorted input to PostgreSQL crosstab() ```sql -- BAD — missing ORDER BY causes values to appear in wrong columns SELECT * FROM crosstab( 'SELECT dept, quarter, revenue FROM sales GROUP BY dept, quarter' ) AS ct(dept TEXT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC); ``` ### Correct: Always sort by row-name column and provide category query ```sql -- GOOD — sorted input + explicit category list SELECT * FROM crosstab( 'SELECT dept, quarter, revenue FROM sales GROUP BY dept, quarter ORDER BY dept, quarter', $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$ ) AS ct(dept TEXT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC); ``` ### Wrong: String concatenation in dynamic pivot (SQL injection risk) ```sql -- BAD — direct string interpolation allows SQL injection SET @sql = 'SELECT * FROM t PIVOT (SUM(val) FOR col IN (' + @user_input + '))'; EXEC(@sql); ``` ### Correct: Use QUOTENAME and validate inputs ```sql -- GOOD — QUOTENAME escapes identifiers; validate against whitelist SELECT @columns = STRING_AGG(QUOTENAME(col_value), ', ') FROM (SELECT DISTINCT col_value FROM t WHERE col_value IN (SELECT allowed_value FROM whitelist)) AS safe; SET @sql = N'SELECT * FROM t PIVOT (SUM(val) FOR col IN (' + @columns + N')) AS pvt;'; EXEC sp_executesql @sql; ``` ### Wrong: Using SUM(CASE) when values are not aggregatable ```sql -- BAD — SUM on non-numeric status values silently returns 0 or errors SELECT id, SUM(CASE WHEN attr = 'color' THEN value END) AS color FROM attributes GROUP BY id; ``` ### Correct: Use MAX or MIN for non-aggregated single values ```sql -- GOOD — MAX returns the single value per group for non-numeric columns SELECT id, MAX(CASE WHEN attr = 'color' THEN value END) AS color, MAX(CASE WHEN attr = 'size' THEN value END) AS size FROM attributes GROUP BY id; ``` ## Common Pitfalls - **Missing tablefunc extension**: PostgreSQL `crosstab()` fails with "function crosstab(unknown) does not exist". Fix: `CREATE EXTENSION IF NOT EXISTS tablefunc;` — requires superuser or database owner. [src1] - **Unsorted crosstab input**: Values silently land in wrong columns when the source query is not sorted by the row-name column. Fix: Always add `ORDER BY row_name_col, category_col` to the inner query. [src1] - **NULL swallowing in UNPIVOT**: SQL Server UNPIVOT silently removes rows with NULL values — data loss goes unnoticed. Fix: Use `CROSS APPLY (VALUES ...)` instead, or wrap columns in `ISNULL(col, default)`. [src2] - **MySQL GROUP_CONCAT truncation**: Dynamic pivot strings truncated at `group_concat_max_len` (default 1024 bytes). Fix: `SET SESSION group_concat_max_len = 1000000;` before the query. [src7] - **Oracle UNPIVOT NULL exclusion**: Default is EXCLUDE NULLS. Fix: Add `INCLUDE NULLS` keyword. [src4] - **Aggregate function mismatch**: Using `SUM` for single-valued pivots returns wrong results when duplicates exist; using `MAX` for aggregations ignores lower values. Fix: Match the aggregate to the semantic meaning — `SUM` for totals, `MAX`/`MIN` for single values, `COUNT` for frequencies. [src3] - **Column count mismatch in crosstab**: The output column definition in `AS ct(...)` must exactly match the number of category values — extra or missing columns cause errors or silent misalignment. Fix: Count distinct category values and match the column list. [src1] - **Performance on large datasets**: Conditional aggregation (CASE/FILTER) scans the entire table once; UNION ALL unpivot scans it N times (once per column). Fix: For large unpivots, use LATERAL VALUES (single scan) instead of UNION ALL. [src5] ## Version History & Compatibility | Database | PIVOT Support | UNPIVOT Support | Best Alternative | |---|---|---|---| | SQL Server 2005+ | Native `PIVOT` clause | Native `UNPIVOT` clause | `CROSS APPLY (VALUES ...)` for NULL preservation | | Oracle 11g+ (2007) | Native `PIVOT` / `PIVOT XML` | Native `UNPIVOT INCLUDE/EXCLUDE NULLS` | — | | PostgreSQL 8.3+ | `crosstab()` via tablefunc | No native support | `LATERAL (VALUES ...)` for unpivot; `FILTER()` for pivot (9.4+) | | MySQL 5.0+ | No native support | No native support | `CASE + GROUP BY` pivot; `UNION ALL` unpivot | | MariaDB 10.0+ | No native support | No native support | Same as MySQL | | SQLite 3.x | No native support | No native support | `CASE + GROUP BY` pivot; `UNION ALL` unpivot | | DuckDB 0.8+ | Native `PIVOT` / `UNPIVOT` | Native `UNPIVOT` | Modern syntax closest to SQL Server | ## When to Use / When Not to Use | Use When | Don't Use When | Use Instead | |---|---|---| | Generating cross-tab reports with a known, fixed set of column values | Column values are unknown and change frequently | Dynamic SQL with strong input validation, or pivot in application layer | | Transforming EAV (Entity-Attribute-Value) tables into flat rows | The EAV table has hundreds of distinct attributes | Application-layer pivot (pandas, DataFrame) or JSON aggregation | | Denormalizing for BI/reporting queries | The pivoted result will be written back as a permanent table | Materialized view with scheduled refresh | | Unpivoting wide tables for ETL/normalization | Source table has 2-3 columns — already normalized | No transformation needed | | One-time data migration or analysis | Real-time API responses where column count must be fixed | Pre-defined views or application-layer reshaping | ## Important Caveats - Dynamic pivot queries are inherently vulnerable to SQL injection — always use parameterization (`QUOTENAME` on SQL Server, `format('%I', ...)` on PostgreSQL) and validate against a whitelist - `crosstab()` with the single-parameter form (no category query) assigns values positionally — this silently produces wrong results when some row/category combinations are missing - Oracle `PIVOT XML` returns XML-typed columns, not scalar values — useful for dynamic column lists but requires XML parsing downstream - Performance of conditional aggregation degrades linearly with the number of pivot columns — at 50+ columns, consider restructuring the query or using application-layer pivoting - UNPIVOT does not round-trip perfectly with PIVOT — aggregation during pivot merges rows, so unpivoting the result produces fewer rows than the original ## Related Units - [SQL Window Functions](/software/patterns/sql-window-functions/2026) - [Recursive CTE Queries for Hierarchies](/software/patterns/sql-recursive-cte/2026) - [Querying JSON Data in PostgreSQL and MySQL](/software/patterns/sql-json-queries/2026) - [PostgreSQL Slow Queries](/software/debugging/postgresql-slow-queries/2026)