Data engineering
PIVOT, UNPIVOT and conditional aggregation
Preserve grain while reshaping a report.
Understand the concept
PIVOT turns chosen values into columns and applies aggregation. Extra source columns can create unexpected implicit grouping. Prepare only the grouping key, pivot key and measure. Conditional CASE aggregation is often clearer and more flexible.
UNPIVOT turns columns into rows and discards NULL values. Dynamic pivoting creates an identifier list, requiring approved names, QUOTENAME and safe value parameters. These examples use fixed Paid/Pending/Cancelled statuses. Zero and missing-data NULL differ, though showing zero in a count report can be a deliberate reporting choice.
Prepare dashboard columns and long-form data.
Leaving irrelevant columns in a PIVOT source.
One topic. Three levels of depth.
SELECT CustomerId, SUM(CASE WHEN Status = 'Paid' THEN 1 ELSE 0 END) AS Paid FROM lab.SalesOrder GROUP BY CustomerId;Uses explicit conditional aggregation.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Count Paid and Pending orders per customer using CASE.
Show the solution and explanation
SELECT CustomerId, SUM(CASE WHEN Status = 'Paid' THEN 1 ELSE 0 END) AS Paid, SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS Pending FROM lab.SalesOrder GROUP BY CustomerId;Cancelled rows belong to groups but do not increase these measures.