Vertical to Horizontal

Question

Post reply

Vertical to Horizontal

info 58414

Hall of Fame

Points: 3386
More actions
March 29, 2012 at 1:38 am

#391900

Hi,
i want to bring rows in a Detail-Table (vertical) into horizontal Form with a T-SQL Query (max. 3 Columns). Is there a fast Way without a Cursor ?
OLD
--------------------------
StorePhoneNr
471149911-1111111
471149911-1111112
471149911-1111113
...
NEW
--------------------------
Store Phone1....... Phone2......... Phone3
4711 49911-11111 49911-11112 49911-11113
many thanks
😀

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

ColdCoffee

SSC-Dedicated

Points: 39971

March 29, 2012 at 1:40 am

#1466212

Look up PIVOT in SQL MSDN Online.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 2

Try this

DECLARE @DetailTable TABLE(Store INT, PhoneNr VARCHAR(30))

INSERT INTO @DetailTable(Store, PhoneNr)

SELECT 4711, '49911-1111111' UNION ALL

SELECT 4711, '49911-1111112' UNION ALL

SELECT 4711, '49911-1111113';

WITH CTE AS (

SELECT Store, PhoneNr,

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

FROM @DetailTable)

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;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

info 58414 Hall of Fame Points: 3386 More actions · Answer 3

info 58414

Hall of Fame

Points: 3386

March 29, 2012 at 1:45 am

#1466214

perfect :kiss:

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

LIke this

IF OBJECT_ID('TempDB..#Temp') IS NOT NULL

DROP TABLE #Temp;

GO

;

CREATE TABLE #Temp

(

Store INT

,PhoneNr VARCHAR(20)

)

;

INSERT INTO #Temp (Store,PhoneNr)

SELECT 4711 , '49911-1111111'

UNION ALL SELECT 4711 , '49911-1111112'

UNION ALL SELECT 4711 , '49911-1111113'

;

; WITH CTE AS

(

SELECT T.Store , T.PhoneNr

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

FROM #Temp 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

info 58414 Hall of Fame Points: 3386 More actions · Answer 5

info 58414

Hall of Fame

Points: 3386

March 29, 2012 at 2:00 am

#1466220

also perfect [Kiss]

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

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.

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

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.

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

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 😉

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

imrankhan777 Valued Member Points: 69 More actions · Answer 7

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

imrankhan777 (3/29/2012)
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

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

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.

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

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.

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

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.

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

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 😉

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

info 58414 Hall of Fame Points: 3386 More actions · Answer 9

info 58414

Hall of Fame

Points: 3386

March 30, 2012 at 12:20 am

#1466759

😛

aaron.reese SSChampion Points: 13498 More actions · Answer 10

PIVOT requires that you specify the column values to be displayed as part of the command.

All of the PIVOT solutions posted assume that there is a maximum of 3 phone numbers per store. If there could be potentially more, then your only option is to build the PIVOT SQL statement dynamically. This requires that you get a results set of the RNs and use a CURSOR to iterate through the results set and append the RNs into the "IN(....)" part of the PIVOT SQL statement.

There are plenty of examples of dynamic PIVOT queries available on Google.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 11

Mr. Cadavre,

You may want to try these two against each other. I think the CROSS APPLY is faster:

SET STATISTICS TIME ON

SET STATISTICS IO ON

IF OBJECT_ID('TempDB..#Temp') IS NOT NULL

DROP TABLE #Temp;

GO

;

CREATE TABLE #Temp

(

Store INT

,PhoneNr VARCHAR(20)

)

;

INSERT INTO #Temp (Store,PhoneNr)

SELECT 4711 , '49911-1111111'

UNION ALL SELECT 4711 , '49911-1111112'

UNION ALL SELECT 4711 , '49911-1111113'

UNION ALL SELECT 4712 , '49911-1111121'

UNION ALL SELECT 4712 , '49911-1111151'

UNION ALL SELECT 4713 , '49911-1111155'

UNION ALL SELECT 4714 , '49911-2111155'

UNION ALL SELECT 4714 , '49911-3111155'

UNION ALL SELECT 4714 , '49911-4111155'

UNION ALL SELECT 4714 , '49911-5111155'

;WITH CTE AS (

SELECT Store, PhoneNr

,ROW_NUMBER() OVER (PARTITION BY Store ORDER BY Store,PhoneNr) AS rk

FROM #Temp

)

SELECT Store

,MAX(CASE WHEN n=1 THEN PhoneNr ELSE NULL END) As PhoneNr1

,MAX(CASE WHEN n=2 THEN PhoneNr ELSE NULL END) As PhoneNr2

,MAX(CASE WHEN n=3 THEN PhoneNr ELSE NULL END) As PhoneNr3

FROM CTE

CROSS APPLY (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) x(n)

WHERE rk=n

GROUP BY Store

;WITH CTE AS

(

SELECT T.Store , T.PhoneNr

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

FROM #Temp 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

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St