Luis Cazares (2/11/2014)
Have you tested again?
Luis - I completely flaked out and missed this message. I am truly sorry and feel like a jerk. I fixed the my code and re-tested it. The server I originally tested this on does not exist. On my laptop (4 cpu, 16gb ram, SQL 2014) using the code that I posted above I get the following results against 10M rows:
crosstab, quarters only
SQL Server Execution Times:
CPU time = 3796 ms, elapsed time = 1106 ms.
pivot, quarters only
SQL Server Execution Times:
CPU time = 6344 ms, elapsed time = 1703 ms.
crosstab office and quarters
SQL Server Execution Times:
CPU time = 6297 ms, elapsed time = 1684 ms.
pivot office and quarters
SQL Server Execution Times:
CPU time = 6374 ms, elapsed time = 1751 ms.
I did 10M because the results are pretty close.
-- Itzik Ben-Gan 2001