UNPIVOT a table using CROSS APPLY

8

April 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.

About these ads

8 thoughts on “UNPIVOT a table using CROSS APPLY

  1. PortletPaul says:

    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.

  2. Arthur Olcot says:

    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.

  3. […] 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. […] 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 […]

  5. Vivek says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 428 other followers

%d bloggers like this: