Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Vertical to Horizontal Expand / Collapse
Author
Message
Posted Thursday, March 29, 2012 1:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 5:14 AM
Points: 53, Visits: 180
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
Post #1274906
Posted Thursday, March 29, 2012 1:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:22 PM
Points: 2,262, Visits: 5,421
Look up PIVOT in SQL MSDN Online.
Post #1274909
Posted Thursday, March 29, 2012 1:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555
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;



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1274910
Posted Thursday, March 29, 2012 1:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 5:14 AM
Points: 53, Visits: 180
perfect
Post #1274911
Posted Thursday, March 29, 2012 1:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:22 PM
Points: 2,262, Visits: 5,421
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

Post #1274912
Posted Thursday, March 29, 2012 2:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 5:14 AM
Points: 53, Visits: 180
also perfect [Kiss]
Post #1274917
Posted Thursday, March 29, 2012 2:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,386, Visits: 7,611
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1274922
Posted Thursday, March 29, 2012 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 25, 2012 5:13 AM
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
Post #1275008
Posted Thursday, March 29, 2012 6:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,386, Visits: 7,611
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1275020
Posted Friday, March 30, 2012 12:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 5:14 AM
Points: 53, Visits: 180
Post #1275539
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse