What is the difference between static and Dynamic PIVOT generates the column list automatically at runtime without hardcoding?

A static PIVOT has the column values hardcoded in the query. A Dynamic PIVOT generates the column list automatically at runtime without hardcoding discovers those values at runtime by querying the table — useful when the values change frequently. Use the Dynamic PIVOT Generator for that pattern.

SQL PIVOT Generator — SQL Server PIVOT Query Builder

Generate a production-ready SQL Server PIVOT query in seconds. Enter your table name, row field, column field, value field, and the list of pivot values — the tool assembles the correct T-SQL syntax, handles quoting, and wraps NULLs if needed. No SQL memorization required.

Query Configuration

Table Name required

CTE / Subquery Alias

Row Field required — groups the rows

Column Field required — values become columns

Value Field required

Aggregate

WHERE Filter optional

Pivot Values required

Text values quoted automatically. Numeric values left unquoted.

Column to scan

Detected values:

NULL replacement optional

Generated T-SQL

SQL Server / Azure SQL

📚 Guide & Tutorial: SQL PIVOT Generator: How to Build Pivot Queries for SQL Server — step-by-step walkthrough with examples.

Examples

Example: Monthly revenue by product

Sales table with ProductName, Month, and Revenue columns. Pivot months across the top.

SELECT ProductName, [Jan], [Feb], [Mar], [Apr],
       [May], [Jun], [Jul], [Aug],
       [Sep], [Oct], [Nov], [Dec]
FROM (
    SELECT ProductName, Month, Revenue
    FROM dbo.SalesData
) AS src
PIVOT (
    SUM(Revenue)
    FOR Month IN ([Jan],[Feb],[Mar],[Apr],
                  [May],[Jun],[Jul],[Aug],
                  [Sep],[Oct],[Nov],[Dec])
) AS pvt
ORDER BY ProductName;

Each NULL in the output means no revenue was recorded for that product/month combination. Wrap with ISNULL([Jan], 0) etc. to show zeros.

Example: Headcount by department and year

Employee table. Count employees per department per year.

SELECT Department, [2022], [2023], [2024]
FROM (
    SELECT Department, YEAR(HireDate) AS HireYear, EmployeeID
    FROM dbo.Employees
) AS src
PIVOT (
    COUNT(EmployeeID)
    FOR HireYear IN ([2022],[2023],[2024])
) AS pvt
ORDER BY Department;

Note: numeric pivot values (years) are still wrapped in square brackets but not single-quoted in the IN list.

Example: Max score by student and subject — with NULL replacement

Exam results table. Highest score per student per subject, replace missing with 0.

SELECT StudentName,
       ISNULL([Maths], 0)   AS Maths,
       ISNULL([English], 0) AS English,
       ISNULL([Science], 0) AS Science
FROM (
    SELECT StudentName, Subject, Score
    FROM dbo.ExamResults
    WHERE Term = 'Spring'
) AS src
PIVOT (
    MAX(Score)
    FOR Subject IN ([Maths],[English],[Science])
) AS pvt
ORDER BY StudentName;

What This Tool Does

The SQL PIVOT Generator builds a SQL Server PIVOT query from a simple form. You supply the table name, the field that will become the row labels, the field whose distinct values will become column headers, the value field to aggregate, and the list of pivot values. The tool assembles the correct T-SQL syntax — subquery wrapper, PIVOT operator, IN clause, and optional ISNULL null replacement — and outputs it ready to copy and run.

The generator also supports auto-detection of pivot values: paste a sample of CSV or tab-separated data, point it at the right column, and it extracts all distinct values automatically. This saves time and avoids typos when dealing with long value lists like product names or region codes.

How to Use It

Enter your Table Name exactly as it appears in your database, including schema (e.g. dbo.SalesData)
Set the Row Field — the column whose values will appear down the left side of the result
Set the Column Field — the column whose distinct values will become the new column headers
Set the Value Field and choose an Aggregate Function (SUM is most common)
Enter an optional WHERE filter to scope the data
Enter the Pivot Values manually (comma-separated) or paste sample data for auto-detection
Click Generate SQL — copy the output and run it in SQL Server Management Studio or Azure Data Studio

Supported Patterns

Feature	Supported	Notes
Static PIVOT	✓ Yes	Hardcoded IN list — fastest and most readable
NULL replacement	✓ Yes	Wraps each pivot column in ISNULL(col, value)
WHERE clause	✓ Yes	Applied in the inner subquery
Numeric pivot values	✓ Yes	Years, IDs — not single-quoted, still square-bracketed
Text pivot values	✓ Yes	Automatically single-quoted if required
Dynamic PIVOT	Separate tool	Use the Dynamic PIVOT Generator for runtime column discovery
Multiple aggregates	Not supported	SQL Server PIVOT supports one aggregate per query

Why Use SQL PIVOT?

The PIVOT operator solves a reporting problem that comes up constantly: your data is stored in normalized rows, but the report consumer wants it in a cross-tab format — months across the top, products down the side, revenue in the cells.

Without PIVOT, you would write a series of conditional aggregates:

SELECT ProductName,
       SUM(CASE WHEN Month = 'Jan' THEN Revenue ELSE 0 END) AS Jan,
       SUM(CASE WHEN Month = 'Feb' THEN Revenue ELSE 0 END) AS Feb,
       ...
FROM dbo.SalesData
GROUP BY ProductName;

This works, but it is verbose and error-prone. The PIVOT operator makes the intent explicit and the code shorter. It is also easier to read in code review and simpler to extend.

Privacy & Security

All SQL generation runs in your browser using JavaScript. Your table names, column names, and any pasted sample data never leave your device — nothing is transmitted to any server. You can safely use real production table names and column names without exposing your schema.

The generated SQL is parameterized in structure but uses literal values you provide. If you plan to use dynamic PIVOT with user-supplied input in production, see the Dynamic PIVOT Generator for notes on SQL injection prevention.

Common Problems & Fixes

Problem	Cause	Fix
Incorrect syntax near 'PIVOT'	Compatibility level below 90	Run: ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 130
Column value not appearing in output	Value not in the IN list	Add the missing value to the pivot values field and regenerate
All cells are NULL	Column field name or value mismatch	Check exact spelling — PIVOT values are case-sensitive on case-sensitive collations
Aggregate error on non-numeric column	COUNT(*) not supported in PIVOT	Use COUNT with a specific column: COUNT(ColumnName), not COUNT(*)
'Invalid column name' after PIVOT	Pivot value contains special characters	The generator wraps values in square brackets; ensure no unmatched brackets in the values themselves
Missing rows in output	WHERE clause too restrictive	Test the inner subquery SELECT separately to verify it returns the rows you expect

💡 If your pivot column values change over time and you want columns discovered automatically at runtime, use the Dynamic PIVOT Generator instead. For the reverse operation — converting a wide pivoted table back into rows — the UNPIVOT Generator handles both the native UNPIVOT operator and the CROSS APPLY VALUES pattern. Once generated, paste your query into the SQL Formatter for clean, consistent formatting.

SQL PIVOT Workflow

Static PIVOT fits into a broader SQL analysis workflow:

Dynamic PIVOT — auto-discovers column values at runtime, no hardcoding required
UNPIVOT the data — normalize wide tables back to row format
Visual Pivot Builder — upload CSV data and build a pivot interactively before writing SQL
Format the generated SQL for code review and deployment
Deduplicate source data before pivoting for accurate aggregations

Frequently Asked Questions

What is a SQL PIVOT query?▼

A PIVOT query transforms distinct row values from one column into separate output columns, aggregating a value field for each. It turns a normalized table into a cross-tab or spreadsheet-style report.

Why must I wrap the value field in an aggregate function?▼

SQL Server's PIVOT operator always requires an aggregate function — even when each cell has only one value. SUM is the most common choice. Without it, the query will fail with a syntax error.

What happens if a pivot value is NULL?▼

NULL values in the column field are ignored by the PIVOT operator. Cells where no matching row exists output NULL. Use ISNULL() or COALESCE() in the outer SELECT to replace those with zero or another default.

Can I PIVOT on a numeric column?▼

Yes. Numeric pivot values do not need quotes. The generator handles this automatically — text values get single quotes, numeric values do not.

How do I add a WHERE clause?▼

Enter your WHERE condition in the optional filter field — for example Year = 2024. It is added before the PIVOT clause in a subquery or CTE.

What is the difference between static and dynamic PIVOT?▼

A static PIVOT has the column values hardcoded in the query. A dynamic PIVOT discovers those values at runtime by querying the table — useful when the values change frequently. Use the Dynamic PIVOT Generator for that pattern.