|
|
|
|
|
|
|
|
|
Unpivot Data with a Cross Join in SQL Server
We've all created pivot tables, right? Unpivot is the opposite, taking pivoted (denormalized) data and returning to to a row based orientation that usually fits our needs better. SQL MVP Plamen Ratchen provides a simple demonstration of how to do this by leveraging a cross join - a technique that works in SQL 2000!
|
|
|
|
Duration:
|
4
mins
7
secs
|
|
Skill Level:
|
100
|
|
Rating:
|
4.28
out of 5
|
|
Publish Date:
|
November 06, 2008
|
|
|
|
About the Author
|
|
Plamen Ratchev has over two decades experience in the software architecture and development field. He has enjoyed building solutions for UNIX, DOS and Windows platforms, with main focus on SQL Server since version 4.21.
Plamen is founder of Tangra, specializing in relational database applications analysis, implementation, and tuning. His particular interest is in design patterns, performance a...
|
|
References
|
|
Comments
|
|
Useful technique!
|
|
|
I would have liked this done in "dummies" format. Needed more detail.
|
|
|
simple and usefull
|
|
|
Very nice example.
|
|
|
Great example and very simple to look at, but it got me thinking. I started thinking about the cost of doing a CROSS JOIN. So I built the following example that does a UNION ALL and then a CROSS JOIN. I then compared the costs by using profiler. Looks like Read I/O' and DURATION where higher on the CROSS JOIN method.
Here is my test script:
SET NOCOUNT ON;
-- Create Sample Table
CREATE TABLE SalesByQuarter (
SalesYear int PRIMARY KEY
,FirstQtr DECIMAL (9,2)
,SecondQtr DECIMAL (9,2)
,ThirdQtr DECIMAL (9,2)
,FourthQtr DECIMAL (9,2)
);
-- Insert Sample Date
INSERT INTO SalesByQuarter VALUES (2007, 12345.67, 23456.89, 345678.90, 456789.01);
INSERT INTO SalesByQuarter VALUES (2008, 567890.12, 678901.23, 789012.34, 890123.45);
INSERT INTO SalesByQuarter VALUES (2009, 901234.56, 1023456.78, 1123456.78, 54321.09);
-- Display Sample Data
SELECT * FROM SalesByQuarter
GO
-- UNION ALL WAY to UNPIVOT
SELECT SalesYear
,Qtr as SalesQtr
,Sales
FROM
(SELECT * FROM
(SELECT SalesYear, FirstQtr AS Sales, 1 Qtr FROM SalesByQuarter) Q1
UNION ALL
(SELECT SalesYear, SecondQtr AS Sales, 2 Qtr FROM SalesByQuarter)
UNION ALL
(SELECT SalesYear, ThirdQtr AS Sales, 3 Qtr FROM SalesByQuarter)
UNION ALL
(SELECT SalesYear, FourthQtr AS Sales, 4 Qtr FROM SalesByQuarter) ) QTR
ORDER BY SalesYear, SalesQtr
GO
-- Unpivot Data using CROSS APPLY
SELECT SalesYear
,Qtr AS SalesQtr
,CASE WHEN Q.Qtr = 1 THEN FirstQtr
WHEN Q.Qtr = 2 THEN SecondQtr
WHEN Q.Qtr = 3 THEN ThirdQtr
WHEN Q.Qtr = 4 THEN FourthQtr
END as Sales
FROM SalesByQuarter
CROSS JOIN
(SELECT 1 AS QTR
UNION ALL SELECT 2 AS QTR
UNION ALL SELECT 3 AS QTR
UNION ALL SELECT 4 AS QTR
) AS Q
ORDER BY SalesYear, SalesQtr
DROP TABLE SalesByQuarter
|
|
|
Disregard my prior comment. The script I was running included the stats for DROP TABLE in with my CROSS JOIN command. As it turns out the CROSS JOIN does fewer IO's UNION ALL method. \
Sorry for the confusion.
|
|
|
great
|
|
|
super
|
|
|
Clear and concise!
|
|
|
I'd like to see the execution plan for this query since I think there are faster techniques available to get this work done.
Thx
L.
|
|
|
Simple and very useful technique
|
|
|
I have pivoted data perhaps twice, ever, so I am not very experienced with it. This example was elegant, simple, and right on my level. GREAT!
|
|
|
I like that the example was not all pretyped. Watching the query typed out gave me more time to think about and absorb what was being demonstrated
|
|
|
Nice stuff... smart use of UNION... and good learning for viewers...
|
|
|
Would be helpful to show intermediate result sets as you build up the final query.
|
|
|
okk
|
|
|
Gosh folks. This one just isn't "right". As usual, Plamen does a fine job in the presentatin but the code just isn't right. It's actually "dangerous" to performance especially if someone tries to use it on a much larger table because it DOES produce a Cartesian Product internally which will be resolved prior to any output. My request as a fellow professional is that you actually remove this particular session from the website because the code is so very dangerous in larger tables and, because of your good names, I'm afraid someone may adopt it.
I don't have room here to show you the correct alternative (ie: without UNPIVOT). If you're interested, please feel free to contact me at jbmoden@ameritech.net.
|
|
|
I wish you provided the code so I could copy and paste it.
|
|
|
Nice video, but maybe a bit more around the cross join / union?
|
|
|
Mat, the source code is attached under the Resources section.
|
|
|
Jeff, your comments about performance are correct. However, the purpose of this video is to demonstrate one technique. This type of unpivoting is standard. Unpivoting is typically one time process to normalize data.
|
|
|
might have been better if did a select of the initial table and compared that to the result at the end...
|
|
|
good info but hard to understand accent
|
|
Maurice Ivory on
2/13/2012
I enjoyed the video...
|
Must Be Logged In
|
|
|
|
|