PIVOT and UNPIVOT in SQL Server

PIVOT   : rows -> columns   (long table  -> wide report)
UNPIVOT : columns -> rows   (wide table  -> long table, good for analysis)

PIVOT syntax:

SELECT <non-pivoted cols>, [Val1], [Val2], ...
FROM ( <source query with exactly the needed columns> ) AS src
PIVOT ( AGG(<value col>) FOR <column whose values become headers> IN ([Val1], [Val2], ...) ) AS pvt;

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table 1: StudentMarks (long format)

DROP TABLE IF EXISTS dbo.StudentMarks;

CREATE TABLE dbo.StudentMarks
(
    StudentName VARCHAR(30) NOT NULL,
    Subject     VARCHAR(20) NOT NULL,
    Term        VARCHAR(10) NOT NULL,
    Marks       INT         NOT NULL
);

INSERT INTO dbo.StudentMarks (StudentName, Subject, Term, Marks) VALUES
('Aarav', 'Maths', 'Term1', 88), ('Aarav', 'Science', 'Term1', 76), ('Aarav', 'English', 'Term1', 91),
('Aarav', 'Maths', 'Term2', 92), ('Aarav', 'Science', 'Term2', 81), ('Aarav', 'English', 'Term2', 89),
('Diya',  'Maths', 'Term1', 67), ('Diya',  'Science', 'Term1', 85), ('Diya',  'English', 'Term1', 78),
('Diya',  'Maths', 'Term2', 74), ('Diya',  'Science', 'Term2', 90),                                    -- English Term2 missing
('Kabir', 'Maths', 'Term1', 95), ('Kabir', 'Science', 'Term1', 93), ('Kabir', 'English', 'Term1', 60),
('Kabir', 'Maths', 'Term2', 97), ('Kabir', 'Science', 'Term2', 88), ('Kabir', 'English', 'Term2', 65);

(17 rows affected)

Topic table 2: QuarterlyTargets (wide format, for UNPIVOT)

DROP TABLE IF EXISTS dbo.QuarterlyTargets;

CREATE TABLE dbo.QuarterlyTargets
(
    Region   VARCHAR(10) PRIMARY KEY,
    Q1Target DECIMAL(12,2),
    Q2Target DECIMAL(12,2),
    Q3Target DECIMAL(12,2),
    Q4Target DECIMAL(12,2)
);

INSERT INTO dbo.QuarterlyTargets (Region, Q1Target, Q2Target, Q3Target, Q4Target) VALUES
('North', 1500000, 1800000, 1700000, 2200000),
('South', 2000000, 2100000, 2300000, 2600000),
('East',  1200000, 1300000, NULL,    1600000),     -- Q3 target not set (NULL)
('West',  1800000, 1900000, 2000000, 2400000);
GO

(4 rows affected)

Part A: Basic PIVOT

Long data first

SELECT * FROM dbo.StudentMarks WHERE Term = 'Term1';

Subjects become columns (Term1 only)

SELECT StudentName, [Maths], [Science], [English]
FROM (
        SELECT StudentName, Subject, Marks
        FROM dbo.StudentMarks
        WHERE Term = 'Term1'
     ) AS src
PIVOT ( SUM(Marks) FOR Subject IN ([Maths], [Science], [English]) ) AS pvt;

Every column in the source that is NOT aggregated or pivoted becomes a GROUP BY column.

Here Term stays -> one row per Student + Term

SELECT StudentName, Term, [Maths], [Science], [English]
FROM (SELECT StudentName, Term, Subject, Marks FROM dbo.StudentMarks) AS src
PIVOT ( SUM(Marks) FOR Subject IN ([Maths], [Science], [English]) ) AS pvt
ORDER BY StudentName, Term;

Pivot + total column (and NULL handling with Isnull)

SELECT StudentName,
       ISNULL([Maths], 0) AS Maths, ISNULL([Science], 0) AS Science, ISNULL([English], 0) AS English,
       ISNULL([Maths], 0) + ISNULL([Science], 0) + ISNULL([English], 0) AS Total
FROM (SELECT StudentName, Subject, Marks FROM dbo.StudentMarks WHERE Term = 'Term2') AS src
PIVOT ( SUM(Marks) FOR Subject IN ([Maths], [Science], [English]) ) AS pvt;

Part B: Business Reports with PIVOT

Monthly sales by Region (months as columns)

SELECT Region, [1] AS Jan, [2] AS Feb, [3] AS Mar, [4] AS Apr, [5] AS May, [6] AS Jun
FROM (
        SELECT Region, MONTH(SaleDate) AS SaleMonth, TotalAmount
        FROM dbo.Sales
     ) AS src
PIVOT ( SUM(TotalAmount) FOR SaleMonth IN ([1], [2], [3], [4], [5], [6]) ) AS pvt
ORDER BY Region;

Number of orders per Status for each year

SELECT OrderYear, [Delivered], [Shipped], [Pending], [Cancelled]
FROM (SELECT YEAR(OrderDate) AS OrderYear, Status, OrderID FROM dbo.Orders) AS src
PIVOT ( COUNT(OrderID) FOR Status IN ([Delivered], [Shipped], [Pending], [Cancelled]) ) AS pvt;

Same report Without the PIVOT keyword: "conditional aggregation"

(works in every database, easier to add totals - very popular in interviews)

SELECT YEAR(OrderDate) AS OrderYear,
       SUM(CASE WHEN Status = 'Delivered' THEN 1 ELSE 0 END) AS Delivered,
       SUM(CASE WHEN Status = 'Shipped'   THEN 1 ELSE 0 END) AS Shipped,
       SUM(CASE WHEN Status = 'Pending'   THEN 1 ELSE 0 END) AS Pending,
       SUM(CASE WHEN Status = 'Cancelled' THEN 1 ELSE 0 END) AS Cancelled,
       COUNT(*)                                              AS Total
FROM dbo.Orders
GROUP BY YEAR(OrderDate);

Salary bill by Department and Gender

SELECT DepartmentName, [M] AS Male, [F] AS Female
FROM (
        SELECT d.DepartmentName, e.Gender, e.Salary
        FROM dbo.Employees e JOIN dbo.Departments d ON d.DepartmentID = e.DepartmentID
     ) AS src
PIVOT ( SUM(Salary) FOR Gender IN ([M], [F]) ) AS pvt;

Part C: Dynamic PIVOT (column list not known in advance)

Revenue per customer segment, with one column per Category (built at run time)

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(CategoryName), ', ') WITHIN GROUP (ORDER BY CategoryName)
FROM dbo.Categories
WHERE CategoryID IN (SELECT DISTINCT CategoryID FROM dbo.Products);

PRINT @cols;   -- see the generated column list in Messages tab

SET @sql = N'
SELECT Segment, ' + @cols + N'
FROM (
        SELECT c.Segment, cat.CategoryName, oi.LineTotal
        FROM dbo.OrderItems oi
        JOIN dbo.Orders o       ON o.OrderID = oi.OrderID
        JOIN dbo.Customers c    ON c.CustomerID = o.CustomerID
        JOIN dbo.Products p     ON p.ProductID = oi.ProductID
        JOIN dbo.Categories cat ON cat.CategoryID = p.CategoryID
     ) AS src
PIVOT ( SUM(LineTotal) FOR CategoryName IN (' + @cols + N') ) AS pvt;';

EXEC sys.sp_executesql @sql;
GO

, [Desks], [Desktops], [Laptops], [Mobiles], [Office Chairs], [Stationery]

Part D: UNPIVOT (columns -> rows)

Wide data

SELECT * FROM dbo.QuarterlyTargets;

UNPIVOT: each quarter becomes a row (note: NULL values are Dropped - East Q3 disappears)

SELECT Region, Quarter, Target
FROM dbo.QuarterlyTargets
UNPIVOT ( Target FOR Quarter IN (Q1Target, Q2Target, Q3Target, Q4Target) ) AS unpvt
ORDER BY Region, Quarter;

Alternative with CROSS APPLY + VALUES: keeps NULLs and allows renaming (recommended)

SELECT t.Region, v.Quarter, v.Target
FROM dbo.QuarterlyTargets t
CROSS APPLY (VALUES ('Q1', t.Q1Target),
                    ('Q2', t.Q2Target),
                    ('Q3', t.Q3Target),
                    ('Q4', t.Q4Target)) AS v(Quarter, Target)
ORDER BY t.Region, v.Quarter;

Real use: compare Unpivoted targets with actual sales (Q1, Q2 2025)

WITH Targets AS
(
    SELECT Region, CAST(RIGHT(LEFT(Quarter, 2), 1) AS INT) AS Qtr, Target
    FROM dbo.QuarterlyTargets
    UNPIVOT ( Target FOR Quarter IN (Q1Target, Q2Target, Q3Target, Q4Target) ) AS u
),
Actuals AS
(
    SELECT Region, DATEPART(QUARTER, SaleDate) AS Qtr, SUM(TotalAmount) AS Actual
    FROM dbo.Sales
    GROUP BY Region, DATEPART(QUARTER, SaleDate)
)
SELECT t.Region, t.Qtr, t.Target, a.Actual,
       CAST(100.0 * a.Actual / t.Target AS DECIMAL(6,2)) AS AchievedPct
FROM Targets t
JOIN Actuals a ON a.Region = t.Region AND a.Qtr = t.Qtr
ORDER BY t.Region, t.Qtr;

Round trip: PIVOT then UNPIVOT back

WITH Wide AS
(
    SELECT StudentName, [Maths], [Science], [English]
    FROM (SELECT StudentName, Subject, Marks FROM dbo.StudentMarks WHERE Term = 'Term1') s
    PIVOT (SUM(Marks) FOR Subject IN ([Maths], [Science], [English])) p
)
SELECT StudentName, Subject, Marks
FROM Wide
UNPIVOT (Marks FOR Subject IN ([Maths], [Science], [English])) u;

Practice Questions

1. Pivot Sales : Products as rows, Regions as columns, Quantity summed.
2. Pivot Orders : Customers as rows, Years (2024, 2025) as columns, order count.
3. Write a dynamic pivot with one column per MONTH found in the Orders table.
4. Unpivot the Customers table so Email and Phone become rows (ContactType, ContactValue).
5. Re-write B1 using conditional aggregation (CASE WHEN).