And this is why I really love cross-tabs. Pivot becomes completely annoying with multiple columns or calculations being pivoted. I struggled to get right the pivot approach.
Again, any comments are welcome.
Code used for the test:
IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p(id int primary key, qtr tinyint, sales int);
DECLARE @rows int = 1000000;
WITH iTally(n) AS
(SELECT TOP(@rows) ROW_NUMBER() OVER (ORDER BY (SELECT ($)))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT #p
SELECTn,
ceiling(4*rand(convert(varbinary, newid()))),
ceiling(80*rand(convert(varbinary, newid())))+20
FROM iTally;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET NOCOUNT ON;
PRINT 'Normal Cross-tab'
SET STATISTICS TIME ON;
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4,
COUNT(CASE qtr WHEN 1 THEN sales END) AS Count_q1,
COUNT(CASE qtr WHEN 2 THEN sales END) AS Count_q2,
COUNT(CASE qtr WHEN 3 THEN sales END) AS Count_q3,
COUNT(CASE qtr WHEN 4 THEN sales END) AS Count_q4
FROM #p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Normal Pivot'
SET STATISTICS TIME ON;
SELECT pivottbl.[1] AS q1, pivottbl.[2] AS q2, pivottbl.[3] AS q3, pivottbl.[4] AS q4,
pivottbl2.[1] AS Count_q1, pivottbl2.[2] AS Count_q2, pivottbl2.[3] AS Count_q3, pivottbl2.[4] AS Count_q4
FROM (SELECT qtr, sales FROM #p) AS p
PIVOT(SUM(p.sales) FOR p.qtr IN ([1],[2],[3],[4])) AS pivottbl
JOIN (SELECT qtr, sales FROM #p) AS p2
PIVOT(COUNT(p2.sales) FOR p2.qtr IN ([1],[2],[3],[4])) AS pivottbl2 ON 1 = 1;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Pre-aggregated Cross-tab'
SET STATISTICS TIME ON;
--where I was stuck
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4,
SUM(CASE qtr WHEN 1 THEN Cnt END) AS Count_q1,
SUM(CASE qtr WHEN 2 THEN Cnt END) AS Count_q2,
SUM(CASE qtr WHEN 3 THEN Cnt END) AS Count_q3,
SUM(CASE qtr WHEN 4 THEN Cnt END) AS Count_q4
FROM (SELECT qtr, SUM(sales) sales, COUNT(*) Cnt
FROM #p
GROUP BY qtr)p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Pre-aggregated Pivot'
SET STATISTICS TIME ON;
--where I was stuck
SELECT pivottbl.[1] AS q1, pivottbl.[2] AS q2, pivottbl.[3] AS q3, pivottbl.[4] AS q4,
pivottbl2.[1] AS Count_q1, pivottbl2.[2] AS Count_q2, pivottbl2.[3] AS Count_q3, pivottbl2.[4] AS Count_q4
FROM (SELECT qtr, SUM(sales) sales FROM #p GROUP BY qtr) AS p
PIVOT(SUM(p.sales) FOR p.qtr IN ([1],[2],[3],[4])) AS pivottbl
JOIN (SELECT qtr, COUNT(*) Cnt FROM #p GROUP BY qtr) AS p2
PIVOT(SUM(Cnt) FOR p2.qtr IN ([1],[2],[3],[4])) AS pivottbl2 ON 1 = 1;
SET STATISTICS TIME OFF;
Results:
Normal Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server Execution Times:
CPU time = 2160 ms, elapsed time = 2120 ms.
Normal Pivot
SQL Server Execution Times:
CPU time = 2540 ms, elapsed time = 1741 ms.
Pre-aggregated Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server Execution Times:
CPU time = 1160 ms, elapsed time = 475 ms.
Pre-aggregated Pivot
SQL Server Execution Times:
CPU time = 1740 ms, elapsed time = 1271 ms.