Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Vertical to Horizontal


Vertical to Horizontal

Author
Message
info 58414
info 58414
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 267
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
--------------------------
Store PhoneNr
4711 49911-1111111
4711 49911-1111112
4711 49911-1111113
...

NEW
--------------------------
Store Phone1....... Phone2......... Phone3
4711 49911-11111 49911-11112 49911-11113

many thanks
:-D
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
Look up PIVOT in SQL MSDN Online.
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2091 Visits: 22794
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
info 58414
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 267
perfect Kiss
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
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
info 58414
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 267
also perfect [Kiss]
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8437
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 ;-)


Forever trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
imrankhan777
imrankhan777
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 10
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
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8437
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
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 ;-)


Forever trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
info 58414
info 58414
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 267
:-P
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search