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

Unpivoting multiple columns. Expand / Collapse
Author
Message
Posted Friday, April 15, 2011 11:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:37 AM
Points: 154, Visits: 685
hi all,
I want to unpivot multiple columns in my table and i came across this article

http://mangalpardeshi.blogspot.com/2009/04/unpivot-multiple-columns.html

This blog is very nice ,but the problem i have is i don't have an ID column in my table , i am not getting good results with out the where condition
WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1) in the blog .

Is there any way i can unpivot multiple columns for the example in the blog with out a ID(unique) column?




Post #1094318
Posted Saturday, April 16, 2011 8:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
srilu_bannu (4/15/2011)
hi all,
I want to unpivot multiple columns in my table and i came across this article

http://mangalpardeshi.blogspot.com/2009/04/unpivot-multiple-columns.html

This blog is very nice ,but the problem i have is i don't have an ID column in my table , i am not getting good results with out the where condition
WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1) in the blog .

Is there any way i can unpivot multiple columns for the example in the blog with out a ID(unique) column?


There's a huge performance problem associated with the code in the blog. It has an "Accidental CUBE Join" (Also known as a Double Cross Join) it it. If you use the test data and the code provided in that article and run it with the Actual Execution Plan turned on, you'll find an arrow which shows that 27 rows (3 sets * 3 rows * 3 unpivots) have been spawned interally. With the addition of a 4th row, 36 internal rows are generated. With 1000 rows, 9000 internal rows would be generated. In other words, the code will take 9 times longer and use 9 times the number of resources than it needs to.

Further, as you've already found out, the code also relies on joins that you may not have available.

Try something like this, instead... it only creates the same number of internal rows as what will appear in the final output and it was done without the ID column that you say you don't have... and the code is a whole lot less complex, as well. This code has been tested against the code provided in the blog article you provided the link for.

 SELECT s.Product, d.Supplier, d.City
FROM dbo.Suppliers s
CROSS APPLY (
SELECT Supplier1, City1 UNION ALL
SELECT Supplier2, City2 UNION ALL
SELECT Supplier3, City3
) d (Supplier, City)
GO


Hat's off to Paul White for first showing me this method.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1094553
Posted Thursday, May 2, 2013 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 1:15 AM
Points: 2, Visits: 17
Just what I needed !!!

Hats off for sharing this very nice solution!
Post #1448850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse