Unpivotting by CROSS APPLY and VALUES

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT
Published:
Updated:
Edited by: Andrew Leniart
Using a combination of CROSS APPLY and VALUES one can efficiently "unpivot" data into normalised results that are far better for analysis.
A long while back I stumbled over a blog by Kenneth Fisher showing a method for unpivoting using CROSS APPLY and VALUES which led me to expand on the concept to see if I could produce normalised tables from the pivoted data.
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
;

See this as a demo here.

I was pleased with the use of dense_rank() to provide repeatable id's through this process

The blog author also led me here which explores the efficiency of this technique and the results are it is faster than unpivot. I also like the syntax better as this cross apply/values approach overuse of unpivot. Useful, at the very least, as an alternative.
2
759 Views
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community