A discussion came up at my job on the most efficient way to return subtotals and totals in one row, something like:
|Total A||Total B||Total C||Grand Total|
I had assumed that PIVOT would be the most straightforward method, but others preferred using CASE statements. So which method is more efficient?
I posted a script on GitHub to build a table with 6 million records to run both kinds of queries against. The results of each will be:
The PIVOT query:
select [Red], [Blue], [White], ([Red] + [Blue] + [White]) as Total from ( select RecordColor from dbo.TestPivotCase ) as a pivot ( count(RecordColor) for RecordColor in ([Red], [White], [Blue]) )as p;
The CASE query:
select sum(case when RecordColor = 'Red' then 1 else 0 end) as [Red], sum(case when RecordColor = 'Blue' then 1 else 0 end) as [Blue], sum(case when RecordColor = 'White' then 1 else 0 end) as [White], count(*) as [Total] from dbo.TestPivotCase;
So both methods have a similar execution plan and run time, with a slight edge to the PIVOT method.