Always like to test when there are multiple ways to perform a task.

So, lets build some sample data.

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

SET NOCOUNT ON;

--999,999 Random rows of data (divides by 3)

SELECT Store,

LEFT(CAST((ABS(CHECKSUM(NEWID())) % 99999) + 10000 AS VARCHAR(6)),5)+'-'+

LEFT(CAST((ABS(CHECKSUM(NEWID())) % 999999) + 100000 AS VARCHAR(7)),6) AS PhoneNr

INTO #testEnvironment

FROM (SELECT rn/3 AS Store

FROM (SELECT TOP 999999

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3) a

) a;

CREATE CLUSTERED INDEX tmp_idx ON #testEnvironment (Store);

CREATE NONCLUSTERED INDEX nc_tmp_idx ON #testEnvironment (Store, PhoneNr);

Now, have a look at the cross tabs and the pivot.

WITH CTE AS (

SELECT Store, PhoneNr,

ROW_NUMBER() OVER(PARTITION BY Store ORDER BY PhoneNr) AS rn

FROM #testEnvironment)

SELECT Store,

MAX(CASE WHEN rn=1 THEN PhoneNr END) AS Phone1,

MAX(CASE WHEN rn=2 THEN PhoneNr END) AS Phone2,

MAX(CASE WHEN rn=3 THEN PhoneNr END) AS Phone3

FROM CTE

GROUP BY Store;

WITH CTE AS

(

SELECT T.Store , T.PhoneNr

, RN = ROW_NUMBER() OVER(PARTITION BY T.Store ORDER BY T.PhoneNr)

FROM #testEnvironment T

)

SELECT Store

, [1] AS Phone1

, [2] AS Phone2

, [3] AS Phone3

FROM CTE

PIVOT ( MAX(PhoneNr) FOR RN IN ([1],[2],[3])) Pivot_Handle

Query performance: -

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--------------------------------------------------------------------------------

CROSS TABS

--------------------------------------------------------------------------------

Warning: Null value is eliminated by an aggregate or other SET operation.

Table '#testEnvironment____________________________________________________________________________________________________00000000003C'. Scan count 1, logical reads 4230, physical reads 6, read-ahead reads 3624, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 860 ms, elapsed time = 3177 ms.

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--------------------------------------------------------------------------------

PIVOT

--------------------------------------------------------------------------------

Table '#testEnvironment____________________________________________________________________________________________________00000000003C'. Scan count 1, logical reads 4230, physical reads 1, read-ahead reads 1268, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 844 ms, elapsed time = 3192 ms.

All code together so you can re-run these tests yourself.

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

SET NOCOUNT ON;

--999,999 Random rows of data (divides by 3)

SELECT Store,

LEFT(CAST((ABS(CHECKSUM(NEWID())) % 99999) + 10000 AS VARCHAR(6)),5)+'-'+

LEFT(CAST((ABS(CHECKSUM(NEWID())) % 999999) + 100000 AS VARCHAR(7)),6) AS PhoneNr

INTO #testEnvironment

FROM (SELECT rn/3 AS Store

FROM (SELECT TOP 999999

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3) a

) a;

CREATE CLUSTERED INDEX tmp_idx ON #testEnvironment (Store);

CREATE NONCLUSTERED INDEX nc_tmp_idx ON #testEnvironment (Store, PhoneNr);

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT REPLICATE('-',80);

PRINT 'CROSS TABS'

PRINT REPLICATE('-',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH CTE AS (

SELECT Store, PhoneNr,

ROW_NUMBER() OVER(PARTITION BY Store ORDER BY PhoneNr) AS rn

FROM #testEnvironment)

SELECT Store,

MAX(CASE WHEN rn=1 THEN PhoneNr END) AS Phone1,

MAX(CASE WHEN rn=2 THEN PhoneNr END) AS Phone2,

MAX(CASE WHEN rn=3 THEN PhoneNr END) AS Phone3

FROM CTE

GROUP BY Store;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT REPLICATE('-',80);

PRINT 'PIVOT'

PRINT REPLICATE('-',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH CTE AS

(

SELECT T.Store , T.PhoneNr

, RN = ROW_NUMBER() OVER(PARTITION BY T.Store ORDER BY T.PhoneNr)

FROM #testEnvironment T

)

SELECT Store

, [1] AS Phone1

, [2] AS Phone2

, [3] AS Phone3

FROM CTE

PIVOT ( MAX(PhoneNr) FOR RN IN ([1],[2],[3])) Pivot_Handle

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

All in all, they appear to be the same in this case. I've come across more cases where the cross tabs method is faster than where the pivot is, but occasionally the pivot is faster. I guess you need to replicate your exact DDL for the table then try populating it with a lot of data and testing yourself. I would've included the correct table structures in this test setup script, but since no DDL was provided it meant guessing instead 😉