Here is the high level construct: the code below is an example which has been taken from:
http://sqlstudies.com/2013/04/01/unpivot-a-table-using-cross-apply/. Please use the link to get the complete code set.
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)
One of the observation that can be made is that there is 5 columns for Question and Answer in the UnPivotMe table, which are finally transformed into rows in the final result.
FirstName LastName Question Answer
Kenneth Fisher What is your first name? Kenneth
Kenneth Fisher What is your favorite color? green
Kenneth Fisher What do you do for a living? Not much
Kenneth Fisher What is 2x3 6
Kenneth Fisher Why? Because
Bob Smith What is your first name? Robert
Bob Smith What is your favorite color? blue
Bob Smith What is 4x7? 28
Bob Smith What is 7x6 Life the Universe and Everything
Bob Smith Why? Why not
No comments:
Post a Comment