How to Use PIVOT in SQL Server: Turn Rows into Columns

João Barros 08 de July de 2026 4 min read

Turning rows into columns is one of the most requested tasks in reporting: instead of a long list of values, we want a summary table with one value per column. The PIVOT operator in SQL Server does exactly that in T-SQL, without exporting the data to Excel. It is very handy for dashboards and monthly reports, where each period needs its own column. It is essentially the SQL equivalent of an Excel pivot table.

Prerequisites

A SQL Server instance (2012 or later) or an Azure SQL database.
SQL Server Management Studio (SSMS) or Azure Data Studio to run the queries.
Permission to create a table in a test database.
Basic knowledge of SELECT and GROUP BY.

Step 1: Prepare sample data

First we create a small sales table by region and quarter. This lets you follow the example even without a real database at hand.

CREATE TABLE Vendas (
    Regiao    VARCHAR(20),
    Trimestre VARCHAR(2),
    Valor     DECIMAL(10,2)
);

INSERT INTO Vendas (Regiao, Trimestre, Valor) VALUES
('Norte', 'Q1', 1000), ('Norte', 'Q2', 1500),
('Norte', 'Q3', 1200), ('Norte', 'Q4', 1800),
('Sul',   'Q1',  900), ('Sul',   'Q2', 1100),
('Sul',   'Q3', 1300), ('Sul',   'Q4', 1600);

Step 2: See the problem (long format)

A simple query returns one row for each region-and-quarter combination. It is correct, but hard to read as a report.

SELECT Regiao, Trimestre, Valor
FROM Vendas
ORDER BY Regiao, Trimestre;

That is eight rows in what we call the long format. The goal is to have one row per region and one column per quarter, so you can compare values at a glance.

Step 3: Apply PIVOT in SQL Server

The PIVOT operator needs three things: a source query, an aggregate function (here SUM), and the list of values that become columns. The names in square brackets, such as [Q1], are column identifiers and must match the values in the Trimestre column.

SELECT Regiao, [Q1], [Q2], [Q3], [Q4]
FROM (
    SELECT Regiao, Trimestre, Valor
    FROM Vendas
) AS origem
PIVOT (
    SUM(Valor)
    FOR Trimestre IN ([Q1], [Q2], [Q3], [Q4])
) AS pivotado
ORDER BY Regiao;

The SUM(Valor) function fills each cell of the table; the FOR Trimestre IN (...) clause states which values from the Trimestre column turn into columns. The result now has only two rows, one per region.

Note two mandatory details: the source subquery must have an alias (here origem) and the PIVOT result must have one too (pivotado). Without these names, SQL Server returns a syntax error.

Step 4: Handle missing values and add totals

If a region has no sales in a quarter, the cell is NULL. Use ISNULL to show zero, and take the chance to add a total column per region.

SELECT
    Regiao,
    ISNULL([Q1], 0) AS Q1,
    ISNULL([Q2], 0) AS Q2,
    ISNULL([Q3], 0) AS Q3,
    ISNULL([Q4], 0) AS Q4,
    ISNULL([Q1],0) + ISNULL([Q2],0) + ISNULL([Q3],0) + ISNULL([Q4],0) AS Total
FROM (
    SELECT Regiao, Trimestre, Valor
    FROM Vendas
) AS origem
PIVOT (
    SUM(Valor)
    FOR Trimestre IN ([Q1], [Q2], [Q3], [Q4])
) AS pivotado
ORDER BY Regiao;

Verify the result

You should see two rows, one per region, with four quarter columns and the Total column. Check the math: Norte adds up to 1000 + 1500 + 1200 + 1800 = 5500 and Sul adds up to 900 + 1100 + 1300 + 1600 = 4900. If the totals match, PIVOT is working as expected.

Tip: PIVOT requires you to know the values that become columns in advance. When that list changes (for example, months from different years), generate the query with dynamic SQL.

Conclusion

With just a few lines you turned a long list into a summary table, directly in SQL Server and without leaving the database. From here, try swapping SUM for AVG or COUNT, or explore the UNPIVOT operator to go the other way around. And if the quarters were months that change every year, how would you generate the columns automatically?