Building a Pivot Table from SQL Server

Clients love asking for a report that has "one row per product and one column per month." That is a pivot table, and while Excel or Power BI can do it on the client side, sometimes it is faster and cleaner to hand back already-pivoted data straight from SQL Server. T-SQL's PIVOT operator does exactly this, and once you understand its slightly unusual syntax it becomes a tool you reach for constantly.

The Row Data We Start With

Say we have a SalesSummary table with one row per product per month — ProductName, SalesMonth, and TotalAmount. A typical query against it returns dozens or hundreds of rows. What the business actually wants to see is each product as a single row, with a column for Jan, Feb, Mar, and so on, each holding that month's total.

The PIVOT Syntax

PIVOT takes three things: an aggregate function to collapse duplicate rows, the column whose distinct values become new column headers, and the list of those values you want to see. It reads backwards from how most people expect at first — you write the aggregate and the source column inside the PIVOT clause, and the target column list inside IN().

SELECT ProductName, [Jan], [Feb], [Mar], [Apr], [May], [Jun]
FROM (
    SELECT ProductName, MonthName, TotalAmount
    FROM SalesSummary
) AS SourceData
PIVOT (
    SUM(TotalAmount)
    FOR MonthName IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun])
) AS PivotTable
ORDER BY ProductName;

The inner SELECT is important — PIVOT only wants to see the three columns it actually needs (the grouping column, the column being spread out, and the value being aggregated). Feed it a SELECT * from the base table and you will usually end up with unexpected extra grouping because of other columns like an OrderId or a CreatedDate hanging around.

When You Do Not Know the Columns Ahead of Time

The example above hardcodes six months, which is fine if the report is always "this year so far." It falls apart the moment the pivoted values are open-ended — a report per customer, per SKU, or across an arbitrary date range picked by the user. For that you build the column list and the query itself as a string and run it with sp_executesql.

DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SELECT @columns = STRING_AGG(QUOTENAME(MonthName), ', ')
    WITHIN GROUP (ORDER BY MIN(SalesMonth))
FROM SalesSummary;

SET @sql = N'
SELECT ProductName, ' + @columns + N'
FROM (
    SELECT ProductName, MonthName, TotalAmount
    FROM SalesSummary
) AS SourceData
PIVOT (
    SUM(TotalAmount)
    FOR MonthName IN (' + @columns + N')
) AS PivotTable
ORDER BY ProductName;';

EXEC sp_executesql @sql;

STRING_AGG (SQL Server 2017+) builds the comma-separated, bracket-quoted column list dynamically, and QUOTENAME protects against a month or category name that happens to contain a space, bracket, or reserved word. Older versions without STRING_AGG can build the same list with FOR XML PATH and STUFF.

• Static PIVOT — use it when the target columns are fixed and known (fiscal months, a fixed set of status codes).
• Dynamic PIVOT — use it when the columns come from user input or grow over time (customers, SKUs, tags).
• UNPIVOT is the mirror operation, turning columns back into rows — handy for normalizing data that was exported in wide format.
• Watch out for NULLs in the pivoted cells — a product with no sales in a given month shows NULL, not 0, unless you wrap the aggregate in ISNULL or COALESCE.

Wrapping Up

PIVOT will not replace a proper BI tool for ad-hoc slicing, but for a fixed report that a stored procedure can hand straight to a grid or an export, it is often the simplest and fastest path — no extra layer reshaping the data after the fact.