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 c

CREATE 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 = 1

WHILE @cnt <= 10
BEGIN

DBCC 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
    )p
PIVOT (
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])
)pvt

INSERT INTO #Results
SELECT '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 AccountNum

INSERT INTO #Results
SELECT '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 AccountNum

INSERT INTO #Results
SELECT 'CrossTab CTE', DATEDIFF(MS,@Start,GETDATE())

SET @cnt = @cnt + 1

END

SELECT Method, AVG(Duration) 'AVGDuration'
  FROM #Results
 GROUP BY Method

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

Method            AVGDuration
CrossTab           6483
CrossTab CTE       6534
Pivot             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          AVGDuration
CrossTab	11544
CrossTab CA	11746
CrossTab CTE	11610
Pivot           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 AccountNum
OPTION (MAXDOP 1)

CrossTab	2548
CrossTab CTE	2559
Pivot		7095

CrossTabs
Table '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.

Pivot
Table '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	7571
CrossTab CTE	7571
Pivot		6999

CrossTabs and Pivot
Table '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.