Transforming Rows to Columns and Back: SQL PIVOT/UNPIVOT Techniques

Transforming Rows to Columns and Back: SQL PIVOT/UNPIVOT Techniques “Rows and columns are just different ways to tell the same story — PIVOT lets you choose the narration.” In the world of reporting and dashboards, how you shape your data is as important as what it says. SQL gives you the power to pivot data (turn rows into columns) and unpivot (flatten columns into rows) — essential skills for: Financial reports Dynamic summaries BI tool exports Analytical models In this post, we’ll walk through step-by-step examples of: Static and dynamic PIVOTs UNPIVOT for flattening tables Use cases for different database engines Base Table: Sales Data CREATE TABLE sales ( id SERIAL PRIMARY KEY, region TEXT, product TEXT, quarter TEXT, revenue DECIMAL ); INSERT INTO sales (region, product, quarter, revenue) VALUES ('North', 'A', 'Q1', 1200), ('North', 'A', 'Q2', 1500), ('North', 'B', 'Q1', 900), ('South', 'A', 'Q1', 800); Static PIVOT (SQL Server) SELECT region, product, [Q1], [Q2] FROM ( SELECT region, product, quarter, revenue FROM sales ) src PIVOT ( SUM(revenue) FOR quarter IN ([Q1], [Q2]) ) pvt; ✅ Output: region product Q1 Q2 North A 1200 1500 North B 900 NULL South A 800 NULL Dynamic PIVOT (SQL Server) When quarters vary dynamically: DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX); SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',') FROM (SELECT DISTINCT quarter FROM sales) q; SET @sql = ' SELECT region, product, ' + @cols + ' FROM ( SELECT region, product, quarter, revenue FROM sales ) src PIVOT ( SUM(revenue) FOR quarter IN (' + @cols + ') ) pvt'; EXEC sp_executesql @sql; ✅ Best for dynamic dashboards, periodical data changes. PostgreSQL/MySQL Alternative (CASE) SELECT region, product, SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS Q1, SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS Q2 FROM sales GROUP BY region, product;

May 12, 2025 - 21:31

Transforming Rows to Columns and Back: SQL PIVOT/UNPIVOT Techniques

“Rows and columns are just different ways to tell the same story — PIVOT lets you choose the narration.”

In the world of reporting and dashboards, how you shape your data is as important as what it says. SQL gives you the power to pivot data (turn rows into columns) and unpivot (flatten columns into rows) — essential skills for:

Financial reports
Dynamic summaries
BI tool exports
Analytical models

In this post, we’ll walk through step-by-step examples of:

Static and dynamic PIVOTs
UNPIVOT for flattening tables
Use cases for different database engines

Base Table: Sales Data

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  region TEXT,
  product TEXT,
  quarter TEXT,
  revenue DECIMAL
);

INSERT INTO sales (region, product, quarter, revenue) VALUES
('North', 'A', 'Q1', 1200),
('North', 'A', 'Q2', 1500),
('North', 'B', 'Q1', 900),
('South', 'A', 'Q1', 800);

Static PIVOT (SQL Server)

SELECT region, product, [Q1], [Q2]
FROM (
  SELECT region, product, quarter, revenue FROM sales
) src
PIVOT (
  SUM(revenue) FOR quarter IN ([Q1], [Q2])
) pvt;

✅ Output:

region	product	Q1	Q2
North	A	1200	1500
North	B	900	NULL
South	A	800	NULL

Dynamic PIVOT (SQL Server)

When quarters vary dynamically:

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

SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;

SET @sql = '
SELECT region, product, ' + @cols + '
FROM (
  SELECT region, product, quarter, revenue FROM sales
) src
PIVOT (
  SUM(revenue) FOR quarter IN (' + @cols + ')
) pvt';

EXEC sp_executesql @sql;

✅ Best for dynamic dashboards, periodical data changes.

PostgreSQL/MySQL Alternative (CASE)

SELECT region, product,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS Q2
FROM sales
GROUP BY region, product;