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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL