Friday, January 17, 2014

UNPIVOT a Table...

One of the common routines that is done while analyzing or reporting information is Pivoting and Unpivoting of data. There are lot of techniques which are employed to achieve the Pivot/Unpivot Transformation of data. I found one example where the CROSS APPLY VALUES operator is used to achieve the unpivot functionality. The implementation is pretty neat and construct is pretty easy to follow, the standard PIVOT, UNPIVOT operators provided by T-SQL sometimes are hard to follow,
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