UNPIVOT a table using CROSS APPLY
23April 1, 2013 by Kenneth Fisher
I generally spend part of each day looking through http://www.stackoverflow.com, http://dba.stackexchange.com or http://www.sqlservercentral.com. I read through questions that have already been answered if they look interesting and answer questions where I can. This is a great way to not only keep up my basic skills but to collect new and interesting bits of knowledge. In this particular case I was going through dba.stackexchange and I ran across someone trying to unpivot a question table. The answer they got surprised me. The answerer used CROSS APPLY with the VALUES keyword. Now I didn’t realize that CROSS APPLY could use the VALUES keyword, and even if I had I’m not sure I would have ever thought of using it to UNPIVOT a table. Here is the link to the question I found. http://dba.stackexchange.com/questions/35620/is-there-a-better-option-than-union-all-for-multiple-selects-from-the-same-row
Now it took me a little while to wrap my head around what they were doing so I’m going to show an example here in the hopes that it will not only help anyone reading this to understand, but help me too!
First a PIVOTed table.
CREATE TABLE UnPivotMe ( FirstName varchar(255) NOT NULL, LastName varchar(255) NOT NULL, Question1 varchar(1000) NOT NULL, Answer1 varchar(1000) NOT NULL, Question2 varchar(1000) NOT NULL, Answer2 varchar(1000) NOT NULL, Question3 varchar(1000) NOT NULL, Answer3 varchar(1000) NOT NULL, Question4 varchar(1000) NOT NULL, Answer4 varchar(1000) NOT NULL, Question5 varchar(1000) NOT NULL, Answer5 varchar(1000) NOT NULL )
And my test data.
INSERT INTO UnPivotMe VALUES
('Kenneth','Fisher','What is your first name?','Kenneth','What is your favorite color?','green','What do you do for a living?','Not much',
'What is 2x3','6','Why?','Because'),
('Bob','Smith','What is your first name?','Robert','What is your favorite color?','blue','What is 4x7?','238',
'What is 7x6','Life the Universe and Everything','Why?','Why not'),
('Jane','Doe','What is your first name?','John','What is your favorite color?','plaid','What do you do for a living?','Door to door salesman',
'What is 3/4','.75','Why?','yes'),
('Prince','Charming','What is your first name?','George','What is your favorite color?','Orange','What do you do for a living?','Not much',
'What is 1235x523','Yea right','Why?','no')
The task is to UnPivot the table so that we end up with something that looks like this.
CREATE TABLE UnPivoted ( FirstName varchar(255) NOT NULL, LastName varchar(255) NOT NULL, Question varchar(1000) NOT NULL, Answer varchar(1000) NOT NULL )
Here is the query.
SELECT UnPivotMe.FirstName, UnPivotMe.LastName, CrossApplied.Question, CrossApplied.Answer FROM UnPivotMe CROSS APPLY (VALUES (Question1, Answer1), (Question2, Answer2), (Question3, Answer3), (Question4, Answer4), (Question5, Answer5)) CrossApplied (Question, Answer)
Here is my attempt at breaking the code into a more easily understandable “psudo code”.
SELECT <Field list constructed of columns from query and cross applied "table"> FROM <Table to be unpivoted> CROSS APPLY ( VALUES -- Note CROSS APPLY then a left paren then VALUES (<First fields to be unpivoted>), (<Second fields to be unpivoted>), (etc) ) -- Close paren from the CROSS APPLY <Alias for the CROSS APPLY> (<Aliases for the unpivoted field list>)
Many many years ago I worked in a call center and we actually had some tables that looked similar to my “UnPivotMe” table. I have to tell you, I REALLY wish this had worked back then.
SQL Studies 
Great post! Never would have considered VALUES ( … ) “the Table Value Constructor” for unpivot. taking this tip a little further (for the sake of normalization):
SELECT
dense_rank() over (order by UnPivotMe.LastName , UnPivotMe.FirstName) as RespondentID
, UnPivotMe.LastName
, UnPivotMe.FirstName
FROM UnPivotMe;
SELECT DISTINCT
dense_rank() over (order by CrossApplied.SeqNo, CrossApplied.Question) as QuestionID
, CrossApplied.Question
FROM UnPivotMe
CROSS APPLY (
VALUES
(1, Question1)
, (2, Question2)
, (3, Question3)
, (4, Question4)
, (5, Question5)
) AS CrossApplied(SeqNo, Question);
SELECT
dense_rank() over (order by UnPivotMe.LastName , UnPivotMe.FirstName) as RespondentID
, QIDS.QuestionID
, CrossApplied.Answer
FROM UnPivotMe
CROSS APPLY (
VALUES
(Question1, Answer1)
, (Question2, Answer2)
, (Question3, Answer3)
, (Question4, Answer4)
, (Question5, Answer5)
) AS CrossApplied(Question, Answer)
INNER JOIN (
SELECT DISTINCT
dense_rank() over (order by CrossApplied.SeqNo, CrossApplied.Question) as QuestionID
, CrossApplied.Question
FROM UnPivotMe
CROSS APPLY (
VALUES
(1, Question1)
, (2, Question2)
, (3, Question3)
, (4, Question4)
, (5, Question5)
) AS CrossApplied(SeqNo, Question)
) AS QIDS ON CrossApplied.Question = QIDS.Question
order by UnPivotMe.LastName , UnPivotMe.FirstName, QIDS.QuestionID;
http://sqlfiddle.com/#!3/2b9c5/1
😉 Cheers
I have to admit I did realize I could use this method to completely normalize the structure I had created. I was trying to do a quick and easy example as much to demonstrate the method as to get it clear in my own head how it works. I probably should have gone the whole way, so thanks for adding the addition to the post! Since I posted this I’ve also found this article on SSC. http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/ that not only discusses the CROSS APPLY .. VALUES method but even goes into some performance testing. I’ve only skimmed it so far but I plan on reading it in more detail later.
I see the flexing (-: lol
Nice. That is a really cool solution to the problem of unpivoting data. I use PIVOT/UNPIVOT from time to time, but I’m definitely going to remember this technique going forward.
[…] when I needed it, and to share something I felt was pretty cool, I posted a blog entry “UNPIVOT a table using CROSS APPLY“. Later I found this great article on http://www.sqlservercentral.com. An Alternative […]
[…] 4 different ways to unpivot a table including your basic UNPIVOT statement, CROSS APPLY, CROSS APPLY with VALUES and a dynamic SQL version for when you have a large number of […]
Hi, I have an issue with SQL. I have described source to destination in the image. could you help me to make out query as expected? detailed in my post http://vivekqry.blogspot.com/2014/02/pivot-unpivot-hierarchy-repetition-of.html
I’ll be happy to take a look but if I were you I would post your question on http://dba.stackexchange.com/. You are almost guaranteed to get a quicker answer than I can give you.
[…] https://sqlstudies.com/2013/04/01/unpivot-a-table-using-cross-apply/ […]
[…] UNPIVOT using CROSS APPLY — https://sqlstudies.com/2013/04/01/unpivot-a-table-using-cross-apply/ (Kenneth Fisher) CREATE TABLE #UnPivotMe ( FirstName varchar(255) NOT NULL, LastName varchar(255) […]
Good post, thanks for the work. However when comparing the results from an unpivot statement to the results of this Cross Apply on the same data, the results were very different to each result set.
Has anyone else done any validation of results between the two?
I’ve validated it several times and seen the technique used in several other places. Do you have a specific unpivot statement you are using that I can look at?
The UNPIVOT command is almost identical to using the APPLY operator. Refer to http://bradsruminations.blogspot.com.au/2010/02/spotlight-on-unpivot-part-1.html for much more detail on this point where he reverse engineers the relationship via execution plans.
I would suspect that any discrepencies would be related to the queries being used, not to UNPIVOT or APPLY themselves.
Nice post. I knew the results were the same but hadn’t thought to look at the query plan in any detail.
[…] 4 different ways to unpivot a table including your basic UNPIVOT statement, CROSS APPLY, CROSS APPLY with VALUES and a dynamic SQL version for when you have a large number of […]
Thanks much for posting this. Yesterday, I got a quick look at a youtube video of Itzik Ben-Gan describing this technique but didn’t get a chance to absorb it.
Your example is excellent
Thanks! That’s pretty cool!
One additional comment, you can also use a set of UNIONS on the table. I stumbled on this page after discovering one of Jeff Moden’s answers to a similar question (pivoting multiple times on a single table).
http://www.sqlservercentral.com/Forums/Topic1094318-338-1.aspx
Very nice! Thanks!
Hello, I just find out today this great summary that help me to put together my own dummy example to share with other colleagues. BIG THANK YOU for the explanation!
“Many many years ago I worked in a call center and we actually had some tables that looked similar to my “UnPivotMe” table. I have to tell you, I REALLY wish this had worked back then.”
Thank you for blessing those of us working on call center data now for this wizardry :p though I think I saw it somewhere else first. It really is a super trick. And why is call center data always organised so bad xD at least, parts of it…
Many years ago I worked in a call center too. I understand completely.
just stumbled upon a view with over 200 columns (and only 70 rows)
some of the column names include weekdays in them or are the same thing repeated 5 times with a different enumeration.
it’s truly mad, but thankfully I just need the ID and name from it, lol