IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;CREATE TABLE #p(id int, qtr tinyint, sales int);DECLARE @rows int = 10000;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 SELECT n, ceiling(4*rand(convert(varbinary, newid()))), ceiling(80*rand(convert(varbinary, newid())))+20 FROM iTally;-- DESIRED RESULTSSELECT 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 q4FROM #p;-- I AM STUCK HERE SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4FROM #p AS pPIVOT(SUM(sales)FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;

SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4FROM (SELECT qtr, sales FROM #p) AS pPIVOT(SUM(sales)FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;

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 q4FROM (SELECT qtr, SUM(sales) sales FROM #p GROUP BY qtr)p;

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 SELECT n, ceiling(4*rand(convert(varbinary, newid()))), ceiling(80*rand(convert(varbinary, newid())))+20 FROM iTally;--DBCC FREEPROCCACHESET NOCOUNT ON;SET STATISTICS TIME ON; --what I need 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 FROM #p; --where I was stuck SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4 FROM ( SELECT qtr, SUM(sales) sales FROM #p GROUP BY qtr) AS p PIVOT( SUM(sales) FOR qtr IN ([1],[2],[3],[4]) ) AS pivottbl;SET STATISTICS TIME OFF;

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 SELECT n, ceiling(4*rand(convert(varbinary, newid()))), ceiling(80*rand(convert(varbinary, newid())))+20 FROM iTally;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGSDBCC FREEPROCCACHE WITH NO_INFOMSGSSET 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 FROM #p;SET STATISTICS TIME OFF;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGSDBCC FREEPROCCACHE WITH NO_INFOMSGSPRINT 'Normal Pivot'SET STATISTICS TIME ON; --where I was stuck SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4 FROM ( SELECT qtr, sales FROM #p) AS p PIVOT( SUM(sales) FOR qtr IN ([1],[2],[3],[4]) ) AS pivottbl;SET STATISTICS TIME OFF;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGSDBCC FREEPROCCACHE WITH NO_INFOMSGSPRINT 'Pre-aggregated Cross-tab'SET STATISTICS TIME ON; --where I was stuckSELECT 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 q4FROM (SELECT qtr, SUM(sales) sales FROM #p GROUP BY qtr)p;SET STATISTICS TIME OFF;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGSDBCC FREEPROCCACHE WITH NO_INFOMSGSPRINT 'Pre-aggregated Pivot'SET STATISTICS TIME ON; --where I was stuck SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4 FROM ( SELECT qtr, SUM(sales) sales FROM #p GROUP BY qtr) AS p PIVOT( SUM(sales) FOR qtr IN ([1],[2],[3],[4]) ) AS pivottbl;SET STATISTICS TIME OFF;

Normal Cross-tabWarning: Null value is eliminated by an aggregate or other SET operation. SQL Server Execution Times: CPU time = 1350 ms, elapsed time = 1352 ms.Normal Pivot SQL Server Execution Times: CPU time = 1340 ms, elapsed time = 1345 ms.Pre-aggregated Cross-tabWarning: Null value is eliminated by an aggregate or other SET operation. SQL Server Execution Times: CPU time = 1060 ms, elapsed time = 292 ms.Pre-aggregated Pivot SQL Server Execution Times: CPU time = 1070 ms, elapsed time = 320 ms.

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 SELECT n, ceiling(4*rand(convert(varbinary, newid()))), ceiling(80*rand(convert(varbinary, newid())))+20 FROM iTally;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGSDBCC FREEPROCCACHE WITH NO_INFOMSGSSET 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_INFOMSGSDBCC FREEPROCCACHE WITH NO_INFOMSGSPRINT '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_q4FROM (SELECT qtr, sales FROM #p) AS pPIVOT(SUM(p.sales) FOR p.qtr IN ([1],[2],[3],[4])) AS pivottblJOIN (SELECT qtr, sales FROM #p) AS p2PIVOT(COUNT(p2.sales) FOR p2.qtr IN ([1],[2],[3],[4])) AS pivottbl2 ON 1 = 1;SET STATISTICS TIME OFF;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGSDBCC FREEPROCCACHE WITH NO_INFOMSGSPRINT 'Pre-aggregated Cross-tab'SET STATISTICS TIME ON; --where I was stuckSELECT 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_q4FROM (SELECT qtr, SUM(sales) sales, COUNT(*) Cnt FROM #p GROUP BY qtr)p;SET STATISTICS TIME OFF;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGSDBCC FREEPROCCACHE WITH NO_INFOMSGSPRINT '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;

Normal Cross-tabWarning: 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-tabWarning: 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.

-- (1) Sample dataIF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;CREATE TABLE #p( id int primary key, office int not null, qtr int not null, sales int not null);GODECLARE @rows int = 2000000;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 SELECT n, ceiling(60*rand(convert(varbinary, newid()))), ceiling(4*rand(convert(varbinary, newid()))), ceiling(80*rand(convert(varbinary, newid())))+20 FROM iTally;GOCREATE INDEX c_oqs ON #p(office) INCLUDE (qtr, sales);CREATE INDEX c_qs ON #p(qtr) INCLUDE (sales);GO-- single aggregate: 1m rows=no paralellism for CROSS-TAB, but yes for piv-- both get paralell plan at 5m rows----------------------------------------------------------------------------- quarters onlySET NOCOUNT ON;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 FROM #p; WITH p AS ( SELECT qtr, SUM(sales) sales FROM #p GROUP BY office, qtr) SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4 FROM p PIVOT ( SUM(sales) FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;SET STATISTICS TIME OFF;GO-- office & quarters onlySET STATISTICS TIME ON; SELECT office, 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 FROM #p GROUP BY office; --piv WITH p AS ( SELECT office, qtr, SUM(sales) sales FROM #p GROUP BY office, qtr) SELECT office, [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4 FROM p PIVOT ( SUM(sales) FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;SET STATISTICS TIME OFF;GO

5M Rows (both create Parelell Plans)crossTab SQL Server Execution Times: CPU time = 3120 ms, elapsed time = 783 ms.pivot SQL Server Execution Times: CPU time = 1560 ms, elapsed time = 396 ms.1M Rows (Only PIVOT creates a Parelell Plan)crossTab SQL Server Execution Times: CPU time = 952 ms, elapsed time = 946 ms.pivot SQL Server Execution Times: CPU time = 841 ms, elapsed time = 224 ms.