That's worse than the other two solutions for a few reasons.

1. That will contain duplicates in the result-set. You are DISTINCT the Store and Phone Nr, then combine them.

Say we have this: -

Store PhoneNr

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

1 53000-219193

1 77571-604268

That is already DISTINCT. Now we add the row_number so we can pivot the data: -

Store PhoneNr Row_Number

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

1 53000-219193 1

1 77571-604268 2

Now you select the Store from the CTE, so you get two Store "1" and use your sub queries to pivot, so you end up with this: -

Store PhoneNr 1 PhoneNr 2

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

1 53000-219193 77571-604268

1 53000-219193 77571-604268

Yes, you could DISTINCT this as well, but the damage is done as far as performance goes.

2. Whereas the other two solutions are hitting the table as little as possible, you are hitting it far more. Try this: -

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

BEGIN

DROP TABLE #testEnvironment;

END;

SET NOCOUNT ON;

--3000 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 3000

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;

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT REPLICATE('-',80);

PRINT 'Bad CROSS TABS'

PRINT REPLICATE('-',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH CTE AS (

SELECT DISTINCT Store, PhoneNr,

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

FROM #testEnvironment)

SELECT Store,(SELECT PhoneNr FROM CTE AS C WHERE C.Store=P.Store AND C.Items=1 )AS Phone1,

(SELECT PhoneNr FROM CTE AS C WHERE C.Store=P.Store AND C.Items=2 )AS Phone2,

(SELECT PhoneNr FROM CTE AS C WHERE C.Store=P.Store AND C.Items=3 )AS Phone3

FROM CTE AS P

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

The results are staggering: -

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

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____________________________________________________________________________________________________000000000042'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 13, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 2 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____________________________________________________________________________________________________000000000042'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 3 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.

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

Bad CROSS TABS

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

Table '#testEnvironment____________________________________________________________________________________________________000000000042'. Scan count 9001, logical reads 135015, physical reads 0, read-ahead reads 13, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 7422 ms, elapsed time = 7442 ms.

IO differences: -

Solution ScanCounts LogicalReads PhysicalReads Read-AheadReads LobLogicalReads LobPhysicalReads LobRead-AheadReads

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

Your Solution 9001 135015 0 13 0 0 0

Pivot Solution 1 15 0 0 0 0 0

Original Cross Tabs Solution 1 15 0 13 0 0 0

Time differences: -

Solution CPU Elapsed

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

Your Solution 7422 7442

Pivot Solution 0 3

Original Cross Tabs Solution 0 3 2

So with 3,000 rows your solution is 2,480 times slower than either of the other two solutions 😉