CREATE TABLE PivotTest (ID INT IDENTITY(1,1),AccountNum INT,TransDate DATETIME,Ammount NUMERIC(8,4))INSERT INTO PivotTest (AccountNum, TransDate, Ammount)SELECT top 1000000 ABS(CHECKSUM(NEWID())) % 1000 AS AccountNum, DATEADD(DD,(ABS(CHECKSUM(NEWID())) % 365),'2010-01-01') AS TransDate, (ABS(CHECKSUM(NEWID())) % 10000000) * 1.0 / 10000 AS Ammount FROM Tally a, Tally b, Tally cCREATE CLUSTERED INDEX UCX_TransDate ON PivotTest(TransDate)CREATE TABLE #Results (Method VARCHAR(16),Duration INT)DECLARE @Start DATETIME, @Acct INT, @mth NUMERIC(15,4), @cnt INT = 1WHILE @cnt <= 10BEGINDBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @Start = GETDATE()SELECT @Acct = AccountNum, @mth = [2010-01-01], @mth = [2010-02-01], @mth = [2010-03-01], @mth = [2010-04-01], @mth = [2010-05-01], @mth = [2010-06-01], @mth = [2010-07-01], @mth = [2010-08-01], @mth = [2010-09-01], @mth = [2010-10-01], @mth = [2010-11-01], @mth = [2010-12-01] FROM ( SELECT AccountNum, DATEADD(MM,DATEDIFF(MM,0,TransDate),0) AS TransMonth, Ammount FROM PivotTest )pPIVOT (SUM(Ammount)FOR TransMonth IN ([2010-01-01],[2010-02-01],[2010-03-01],[2010-04-01], [2010-05-01],[2010-06-01],[2010-07-01],[2010-08-01], [2010-09-01],[2010-10-01],[2010-11-01],[2010-12-01]))pvtINSERT INTO #ResultsSELECT 'Pivot', DATEDIFF(MS,@Start,GETDATE())DBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @Start = GETDATE()SELECT @Acct = AccountNum, @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-01-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-02-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-03-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-04-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-05-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-06-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-07-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-08-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-09-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-10-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-11-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-12-01' THEN Ammount ELSE 0 END) FROM PivotTest GROUP BY AccountNumINSERT INTO #ResultsSELECT 'CrossTab', DATEDIFF(MS,@Start,GETDATE())DBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @Start = GETDATE();WITH Base AS (SELECT AccountNum, DATEADD(MM,DATEDIFF(MM,0,TransDate),0) AS Mnth, Ammount FROM PivotTest)SELECT @Acct = AccountNum, @mth = SUM(CASE WHEN Mnth = '2010-01-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-02-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-03-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-04-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-05-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-06-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-07-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-08-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-09-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-10-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-11-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-12-01' THEN Ammount ELSE 0 END) FROM Base GROUP BY AccountNumINSERT INTO #ResultsSELECT 'CrossTab CTE', DATEDIFF(MS,@Start,GETDATE())SET @cnt = @cnt + 1ENDSELECT Method, AVG(Duration) 'AVGDuration' FROM #Results GROUP BY Method

Method AVGDuration---------------- -----------CrossTab 10409CrossTab CTE 10351Pivot 9149

Method AVGDurationCrossTab 6483CrossTab CTE 6534Pivot 15032

Pivot SQL Server Execution Times: CPU time = 10983 ms, elapsed time = 11484 ms.CROSSTAB SQL Server Execution Times: CPU time = 17847 ms, elapsed time = 5759 ms.CROSSTAB CTE SQL Server Execution Times: CPU time = 18158 ms, elapsed time = 5954 ms.

Method AVGDurationCrossTab 11544CrossTab CA 11746CrossTab CTE 11610Pivot 11389

SELECT @Acct = AccountNum, @mth = SUM(CASE WHEN Mnth = '2010-01-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-02-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-03-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-04-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-05-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-06-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-07-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-08-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-09-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-10-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-11-01' THEN Ammount ELSE 0 END), @mth = SUM(CASE WHEN Mnth = '2010-12-01' THEN Ammount ELSE 0 END) FROM #PivotTest CROSS APPLY ( SELECT DATEADD(MM,DATEDIFF(MM,0,TransDate),0) AS Mnth) a GROUP BY AccountNumOPTION (MAXDOP 1)

CrossTab 2548CrossTab CTE 2559Pivot 7095

CrossTabsTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'PivotTest'. Scan count 9, logical reads 4801, physical reads 34, read-ahead reads 4739, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.PivotTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'PivotTest'. Scan count 1, logical reads 4723, physical reads 5, read-ahead reads 4733, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CrossTab 7571CrossTab CTE 7571Pivot 6999

CrossTabs and PivotTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'PivotTest'. Scan count 1, logical reads 4723, physical reads 5, read-ahead reads 4733, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.