PIVOT and UNPIVOT in SQL Server with Example

The short answer: PIVOT turns row values into columns for reporting (row-to-column transformation), and UNPIVOT does the reverse, turning columns back into rows (column-to-row transformation) so the data can be stored or processed normally again. If you've ever needed to show monthly sales as columns across the top of a dashboard, or you inherited a wide reporting table that needs to go back into a normalized format for an ETL job, you've run into exactly the problem these two T-SQL operators solve.

Most tutorials show PIVOT and UNPIVOT with four rows of toy data and call it a day. That's fine for learning syntax, but it doesn't prepare you for what actually happens in production: month lists that change every reporting period, NULLs where a product had no sales, and a business user asking why last year's report script broke the moment a new month showed up. This article works through a single, realistic scenario from raw transactional data to a pivoted dashboard table, then back through UNPIVOT for storage, with the messy parts included.

We'll build a monthly sales table, pivot it into a wide cross-tab report (a query that summarizes data across two dimensions, similar to a spreadsheet pivot table), handle an unknown or changing set of months with dynamic SQL, deal with missing data, and then unpivot a wide table back into long format for archival or ETL purposes. Along the way we'll also look at conditional aggregation as an alternative to PIVOT, because in a fair number of cases it's actually the better tool.

Before diving into code, it helps to see the transformation itself. PIVOT reshapes a long, narrow table into a wide one; UNPIVOT does the reverse:

Source shape (long format):

ProductName	SalesMonth	SalesAmount
Wireless Mouse	2026-01	1200.00
Wireless Mouse	2026-02	1450.50

Target shape (wide format):

ProductName	2026-01	2026-02
Wireless Mouse	1200.00	1450.50

Prerequisites

To follow along you'll need SQL Server (a recent supported version — the examples here use standard PIVOT/UNPIVOT syntax that has been stable since SQL Server 2005 and works the same way on modern versions including SQL Server 2022, as well as Azure SQL Database) and a client such as SQL Server Management Studio or Azure Data Studio.

You should be comfortable with:

• Basic T-SQL: SELECT, JOIN, GROUP BY, aggregate functions like SUM and COUNT
• Creating tables and inserting sample data
• The general idea of temporary tables or common table expressions (CTEs), which we'll use to stage data

No prior experience with PIVOT or UNPIVOT is assumed.

Setting Up the Case Study Schema

Imagine a mid-sized retailer that tracks sales transactions per product, per month. The raw data lives in a normal, narrow (long format) table — one row per product per month. This is the shape databases like: easy to insert into, easy to index, easy to extend when a new month arrives.

Each setup script below starts with DROP TABLE IF EXISTS (supported on SQL Server 2016 and later, and on Azure SQL Database), so you can re-run them as many times as you like while working through the article without hitting an "object already exists" error.

DROP TABLE IF EXISTS dbo.MonthlySales;
CREATE TABLE dbo.MonthlySales
(
    SalesID     INT IDENTITY(1,1) PRIMARY KEY,
    ProductName VARCHAR(50)  NOT NULL,
    SalesMonth  VARCHAR(7)   NOT NULL, -- format: 'YYYY-MM'
    SalesAmount DECIMAL(10,2) NOT NULL
);

INSERT INTO dbo.MonthlySales (ProductName, SalesMonth, SalesAmount) VALUES
('Wireless Mouse', '2026-01', 1200.00),
('Wireless Mouse', '2026-02', 1450.50),
('Wireless Mouse', '2026-03', 980.00),
('Mechanical Keyboard', '2026-01', 750.00),
('Mechanical Keyboard', '2026-02', 690.25),
('Mechanical Keyboard', '2026-03', 810.00),
('USB-C Hub', '2026-02', 300.00),
('USB-C Hub', '2026-03', 275.50);

Notice USB-C Hub has no January row at all. That's intentional. Real transactional data is sparse — not every product sells every month — and this gap is going to matter once we pivot.

Step 6 later joins the pivoted report against a master list of every product the business sells, so let's create that table now too. Giving it one product with no transactional sales at all — Laptop Stand — is what makes the LEFT JOIN in Step 6 necessary rather than cosmetic.

DROP TABLE IF EXISTS dbo.Products;
CREATE TABLE dbo.Products
(
    ProductName VARCHAR(50) PRIMARY KEY
);

INSERT INTO dbo.Products (ProductName) VALUES
('Wireless Mouse'),
('Mechanical Keyboard'),
('USB-C Hub'),
('Laptop Stand'); -- Laptop Stand has no rows in MonthlySales at all

This long, narrow shape is sometimes called long format data, as opposed to the wide format a spreadsheet-style report needs. Some developers coming from R or Python pandas call this reshaping "spread and gather," but in T-SQL the operators are PIVOT and UNPIVOT.

Step 1: A Basic PIVOT — Rows to Columns

The business wants a report: one row per product, one column per month, with total sales in each cell. That's a classic cross-tab query (a report that summarizes values across two dimensions, like a spreadsheet pivot table).

SELECT ProductName, [2026-01], [2026-02], [2026-03]
FROM
(
    SELECT ProductName, SalesMonth, SalesAmount
    FROM dbo.MonthlySales
) AS SourceData
PIVOT
(
    SUM(SalesAmount)
    FOR SalesMonth IN ([2026-01], [2026-02], [2026-03])
) AS PivotTable;

Here's what's happening. The inner query pulls the three columns we need. PIVOT then takes the values in SalesMonth and spreads them out as new column headers, using SUM(SalesAmount) as the aggregate function in PIVOT that decides what value fills each cell. Where USB-C Hub has no January record, the result shows NULL for that cell rather than zero — PIVOT doesn't manufacture rows that don't exist, it just has nothing to aggregate.

This is the core mechanic worth understanding: PIVOT is really a specialized GROUP BY. It groups by every column not mentioned in the PIVOT clause (here, ProductName), and for each distinct value in the FOR column, it computes the aggregate and puts it in its own column.

Step 2: The Problem With Hardcoded Columns

The query above works, but only for exactly those three months. The moment April's data arrives, someone has to edit the SQL and redeploy. This is the maintainability trap almost every PIVOT tutorial glosses over — column sets that change over time are the normal case for monthly or yearly reporting, not the exception.

The fix is dynamic SQL: building the column list, and the query itself, as a string at runtime based on whatever months actually exist in the data.

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

SELECT @Columns = STRING_AGG(QUOTENAME(SalesMonth), ', ')
    WITHIN GROUP (ORDER BY SalesMonth)
FROM (SELECT DISTINCT SalesMonth FROM dbo.MonthlySales WHERE SalesMonth IS NOT NULL) AS Months;

IF @Columns IS NULL
BEGIN
    PRINT 'No months found to pivot.';
    RETURN;
END;

SET @SQL = N'
SELECT ProductName, ' + @Columns + N'
FROM
(
    SELECT ProductName, SalesMonth, SalesAmount
    FROM dbo.MonthlySales
) AS SourceData
PIVOT
(
    SUM(SalesAmount)
    FOR SalesMonth IN (' + @Columns + N')
) AS PivotTable;';

EXEC sp_executesql @SQL;

QUOTENAME wraps each month value in square brackets so it's safe to use as a column identifier, protecting against names with spaces or special characters. STRING_AGG concatenates the distinct month values into a comma-separated list, ordered chronologically. Filtering out NULL months before aggregating (as in the query above) isn't strictly needed for this dataset, but it's cheap insurance against a future schema change or a bad import introducing a NULL SalesMonth, which would otherwise produce a stray blank column.

The IF @Columns IS NULL check guards against a different edge case: if dbo.MonthlySales has no rows at all, STRING_AGG returns NULL, and concatenating NULL into @SQL with + silently makes the entire string NULL under SQL Server's default settings. Without the guard, EXEC sp_executesql @SQL would then fail with a confusing "Procedure expects parameter '@statement'" error instead of a clear message about there being no data to pivot. The resulting string is executed with sp_executesql, which is the recommended way to run dynamic SQL because it supports parameterization and avoids some of the plan-cache bloat you'd get from EXEC on raw string concatenation.

STRING_AGG requires SQL Server 2017 or later (it's also available in Azure SQL Database). If you're maintaining a report on an older, legacy instance such as SQL Server 2014 or 2016, you'll need to build the same comma-separated column list with the older FOR XML PATH trick instead.

This is what people mean by a dynamic pivot, or dynamic SQL pivot: the column list isn't known until the query runs, so you build it programmatically rather than hardcoding it. It's a genuinely useful pattern, but it comes with a real cost. The query optimizer can't fully reason about a dynamic string ahead of time the way it can with static SQL, and you lose IntelliSense and some static analysis tooling. For a nightly reporting job this tradeoff is usually fine. For a hot path hit thousands of times a minute, I'd think twice.

Step 3: Handling NULLs and Missing Months

Business users generally don't want to see NULL in a report cell — they want zero, or a dash, or nothing rendered at all depending on the tool. Wrapping the pivoted result in an outer query with ISNULL (or COALESCE, which is the ANSI-standard equivalent) handles this cleanly.

SELECT
    ProductName,
    ISNULL([2026-01], 0) AS Jan_2026,
    ISNULL([2026-02], 0) AS Feb_2026,
    ISNULL([2026-03], 0) AS Mar_2026
FROM
(
    SELECT ProductName, SalesMonth, SalesAmount
    FROM dbo.MonthlySales
) AS SourceData
PIVOT
(
    SUM(SalesAmount)
    FOR SalesMonth IN ([2026-01], [2026-02], [2026-03])
) AS PivotTable;

A subtler issue than NULL cells is missing rows entirely. If a product had zero sales in every month of a quarter, it simply won't appear in the pivoted output at all, because PIVOT can only work with rows that exist in the source. If the dashboard needs every product listed regardless of activity, you need to outer join the pivot result against a master product list rather than relying on PIVOT alone — Step 6 shows exactly this pattern. That's a detail most tutorials skip, and it's the kind of thing that shows up as a bug report from the business three weeks after launch.

Step 4: Conditional Aggregation as an Alternative

PIVOT is convenient, but it isn't the only way to do a row-to-column transformation, and it isn't always the clearest one. Conditional aggregation — using CASE WHEN inside an aggregate function — produces the same result with plain, static SQL:

SELECT
    ProductName,
    SUM(CASE WHEN SalesMonth = '2026-01' THEN SalesAmount ELSE 0 END) AS Jan_2026,
    SUM(CASE WHEN SalesMonth = '2026-02' THEN SalesAmount ELSE 0 END) AS Feb_2026,
    SUM(CASE WHEN SalesMonth = '2026-03' THEN SalesAmount ELSE 0 END) AS Mar_2026
FROM dbo.MonthlySales
GROUP BY ProductName;

I'd lean toward this version for anything that doesn't strictly need dynamic columns. It reads clearly to any SQL developer without needing to know PIVOT-specific syntax, it defaults to 0 instead of NULL without an extra wrapping query, and it's trivial to extend with additional conditions (say, only counting sales above a threshold).

PIVOT earns its keep mainly when the column list genuinely needs to be dynamic or when the query is otherwise complex enough that the PIVOT syntax reads more cleanly than a wall of CASE expressions. In terms of raw performance, the two approaches typically produce similar execution plans for the same aggregation, since PIVOT is syntactic sugar over the same grouping mechanics — but this can vary by query shape and indexing, so it's worth checking the actual execution plan on your own data rather than assuming either is faster by default.

Step 5: UNPIVOT — Going Back to Long Format

Now the reverse problem. Suppose the wide report from Step 1 gets exported somewhere, and later you need to load it back into a normalized staging table for an ETL (extract, transform, load) pipeline, or archive it in the same long format as the original MonthlySales table. UNPIVOT handles column-to-row transformation.

DROP TABLE IF EXISTS dbo.WideSalesReport;
CREATE TABLE dbo.WideSalesReport
(
    ProductName VARCHAR(50),
    Jan_2026 DECIMAL(10,2),
    Feb_2026 DECIMAL(10,2),
    Mar_2026 DECIMAL(10,2)
);

INSERT INTO dbo.WideSalesReport VALUES
('Wireless Mouse', 1200.00, 1450.50, 980.00),
('Mechanical Keyboard', 750.00, 690.25, 810.00),
('USB-C Hub', 0.00, 300.00, 275.50);

SELECT ProductName, SalesMonth, SalesAmount
FROM dbo.WideSalesReport
UNPIVOT
(
    SalesAmount FOR SalesMonth IN (Jan_2026, Feb_2026, Mar_2026)
) AS UnpivotTable;

UNPIVOT takes the three month columns and stacks them back into two columns — SalesMonth and SalesAmount — producing one row per product per month, matching the original long format. This is the same reshaping direction people mean when they talk about normalizing a denormalized reporting table for storage.

One practical wrinkle: UNPIVOT requires all the source columns to share a compatible data type, and it silently drops rows where the value is NULL. If USB-C Hub genuinely had no January sales (NULL rather than 0), that row simply wouldn't appear after UNPIVOT. In the example above we stored 0.00 instead of NULL specifically to avoid losing that row — but that's a modeling decision you need to make deliberately, not something UNPIVOT decides for you.

Alternative to UNPIVOT: CROSS APPLY (VALUES)

UNPIVOT is clean, but its all-or-nothing NULL handling is exactly what trips people up above. CROSS APPLY with a row constructor via the VALUES clause does the same column-to-row reshaping, but as ordinary joins and filters — so you decide explicitly whether to keep or drop NULL rows, instead of having UNPIVOT decide for you.

SELECT ProductName, SalesMonth, SalesAmount
FROM dbo.WideSalesReport
CROSS APPLY (
    VALUES
        ('2026-01', Jan_2026),
        ('2026-02', Feb_2026),
        ('2026-03', Mar_2026)
) AS UnpivotValues(SalesMonth, SalesAmount)
WHERE SalesAmount IS NOT NULL; -- remove this line to keep NULL rows

This version also scales better when you need to unpivot several groups of columns at once, since you can list multiple VALUES row sets and join them, which gets unwieldy with plain UNPIVOT. For a single, simple column set, UNPIVOT's syntax is arguably more self-documenting; reach for CROSS APPLY once NULL handling or multiple column groups become a real requirement.

Step 6: Combining Everything With a CTE for a Reporting Pipeline

In a real reporting pipeline you'll often stage the pivot inside a CTE (common table expression, a named temporary result set defined with WITH that exists only for the duration of the query) before joining it against a dimension table, such as a full product list, to make sure every product appears even with zero sales.

WITH SalesPivot AS
(
    SELECT ProductName, [2026-01], [2026-02], [2026-03]
    FROM
    (
        SELECT ProductName, SalesMonth, SalesAmount
        FROM dbo.MonthlySales
    ) AS SourceData
    PIVOT
    (
        SUM(SalesAmount)
        FOR SalesMonth IN ([2026-01], [2026-02], [2026-03])
    ) AS PivotTable
)
SELECT
    p.ProductName,
    ISNULL(sp.[2026-01], 0) AS Jan_2026,
    ISNULL(sp.[2026-02], 0) AS Feb_2026,
    ISNULL(sp.[2026-03], 0) AS Mar_2026
FROM dbo.Products AS p
LEFT JOIN SalesPivot AS sp ON sp.ProductName = p.ProductName;

This uses the dbo.Products table created back in the setup section — the master list of every product the business sells, whether or not it had sales that quarter. The left join guarantees full coverage on the dashboard: Laptop Stand, which has no rows in MonthlySales at all, still shows up with zero in every month instead of silently disappearing.

The CTE keeps the pivot logic isolated and readable rather than nesting subqueries three levels deep. For tools like Power BI or SQL Server Reporting Services (SSRS), it's often better to hand over the long-format data and let the BI tool do the pivoting visually, since those tools are built for that kind of interactive reshaping — reserve the SQL-side PIVOT for cases where the report is static, scheduled, or consumed by something that expects a fixed wide table, like a CSV export or an email report.

How It Works Under the Hood

PIVOT and UNPIVOT aren't special execution engines — they're syntactic constructs that the optimizer translates into more familiar operations. A PIVOT query is rewritten internally as a grouped aggregation, conceptually similar to the conditional aggregation version shown in Step 4. An UNPIVOT query is rewritten as a series of unioned SELECT statements, one per source column, each tagging the row with which column it came from. Understanding this equivalence is useful because it explains why PIVOT can't do anything a well-written GROUP BY or UNION ALL couldn't also do — it's a readability and convenience feature, not a distinct performance tier.

Common Errors and Troubleshooting

Error: "Invalid column name" (Msg 207)

This usually happens with UNPIVOT when the columns listed in the FOR ... IN clause don't match the actual column names in the source table, often after a schema change. Double-check the column list against sys.columns for the table you're unpivoting.

Error: "Invalid object name 'dbo.Products'" (Msg 208)

This will appear if you run Step 6's query before creating the dbo.Products table from the setup section. It's an easy one to hit if you skip straight to the final query — make sure both MonthlySales and Products exist before running the pipeline example.

Behavior: extra columns in the PIVOT subquery silently change the grouping.

This occurs when the inner subquery passed into PIVOT includes extra columns beyond what's needed. PIVOT expects exactly the grouping column, the spreading column, and the value column — any additional columns become part of the implicit GROUP BY, which silently changes the grouping granularity rather than necessarily failing outright. Trim the inner SELECT down to just those three.

Silent problem: wrong or unexpected NULLs after a dynamic pivot.

If the dynamic column list is built with STRING_AGG and the distinct months don't sort the way you expect, columns can appear out of chronological order, which won't error but will confuse anyone reading the report. Always explicitly order the WITHIN GROUP clause when building the column list.

Silent problem: rows disappearing after UNPIVOT.

As mentioned earlier, NULL values in the source columns are dropped by UNPIVOT rather than preserved as NULL rows. If you need to retain them, convert NULLs to a sentinel value like 0 before unpivoting, or use the CROSS APPLY approach shown above, which gives you full control over NULL handling.

Best Practices

• Prefer conditional aggregation over PIVOT when the column set is fixed and known ahead of time; it's easier to read and extend.
• Reach for dynamic PIVOT only when column values genuinely vary at runtime, and always sanitize and order the generated column list explicitly.
• Store your source data in long format; treat wide, pivoted tables as a reporting-layer output, not as your system of record.
• Handle NULLs deliberately with ISNULL or COALESCE rather than letting a report show blank cells with no explanation.
• Push pivoting into the BI tool (Power BI, SSRS) when the report is interactive or exploratory; keep SQL-side PIVOT for scheduled, fixed-shape outputs.
• Test dynamic SQL pivot queries against realistic data volumes before assuming performance parity with static queries — the plan caching behavior differs.

Conclusion

The core distinction in any SQL Server PIVOT vs UNPIVOT example comes down to direction: PIVOT reshapes rows into columns for reporting, UNPIVOT reshapes columns back into rows for storage or further processing, and both are really just readable wrappers around grouping and unioning that the optimizer already knows how to do.

You now have a working pattern for a dynamic pivot that adapts to changing months, a way to handle NULLs and missing data without corrupting the report, and an UNPIVOT (or CROSS APPLY) step for feeding data back into a normalized pipeline. You've also seen where conditional aggregation is the more maintainable choice, which is worth remembering the next time PIVOT syntax feels like overkill for a simple fixed report.

As a next step, try running the dynamic pivot script against a dataset with a full year of months and confirm the columns come back in the right chronological order — that's usually where the first real bug shows up.