DEV Community

Cristian Sifuentes
Cristian Sifuentes

Posted on

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

#sql #database #dataengineering #analytics

Transforming Rows to Columns and Back

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

✅ 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;
Enter fullscreen mode Exit fullscreen mode

✅ 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;
Enter fullscreen mode Exit fullscreen mode

💡 Portable to MySQL, PostgreSQL, Redshift, etc.

UNPIVOT (SQL Server) 

SELECT region, product, quarter, revenue FROM ( SELECT region, product, Q1, Q2 FROM sales_summary ) s UNPIVOT ( revenue FOR quarter IN (Q1, Q2) ) AS unpvt;
Enter fullscreen mode Exit fullscreen mode

✅ Use UNPIVOT for:

  • Normalizing wide data
  • Feeding ETL pipelines
  • Converting tables for inserts

Advanced Use Case: Monthly Sales Matrix 

SELECT product, SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1 THEN revenue END) AS Jan, SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2 THEN revenue END) AS Feb, ... SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 12 THEN revenue END) AS Dec FROM sales GROUP BY product;
Enter fullscreen mode Exit fullscreen mode

✅ Widely used in:

  • Budgeting reports
  • Spreadsheet exports
  • BI pivots

When to Use What?

Method Engines Strength
PIVOT SQL Server Fast, native, readable
CASE All engines Compatible, portable
UNPIVOT SQL Server Clean normalization
Dynamic SQL SQL Server Required for unknown dimensions

Pro Tips

  • Avoid over-pivoting: too many columns can hurt performance
  • Use COALESCE() to fill NULLs when needed
  • Index original table for refresh speed
  • Combine with ROLLUP/CUBE for powerful summaries

Final Thoughts: Data in Motion

PIVOT/UNPIVOT isn’t just syntax—it’s a lens into your data’s story. Mastering this technique empowers you to:

  • Build performant summaries
  • Create dynamic reports
  • Shape your data for any downstream consumer

“Data isn't flat — reshape it.”

#SQL #Pivot #Unpivot #Analytics #Reporting #PostgreSQL #SQLServer #MySQL #AdvancedSQL

Top comments (0)