In prior blog posts I talked about a Standard PIVOT query and a Dynamic PIVOT query. It is only fitting that I talk about how to implement an UNPIVOT query. As DBA’s we commonly unpivot data and turn it into normalized data for storing in a relational data store. Understanding how to use the UNPIVOT query is a great skill to have as a DBA. Just like the PIVOT operator, the UNPIVOT operator was introduced with SQL Server 2005. In this blog post I will show how we can use the UNPIVOT operator.
I got the idea for this blog post as a result from my online grade book. I noticed that the data is displayed as a denormalized set of data. As an instructor I thought it would be interesting to see average, sum, minimum and maximum assignment scores for each student. Below is an example of the display of the online grade book.
I found that writing a simple query to get the sum (adding each column) and average (adding each column and dividing by the number of columns) was not too bad. However, finding the minimum and maximum proved to be a bit tricky. Sure we could implement a CASE WHEN statement, however for each column the complexity and possibility of error increases significantly, and maintainability dang near goes out the window. In comes UNPIVOT to save the day. After unpivoting the data, we simply use the AVG(), SUM(), MIN() and MAX() functions.
Let’s take a look at the basic structure of the UNPIVOT query. Using the UNPIVOT statement, you specify a value and data column, then select each of the columns you want to unpivot into these new columns. The new value and data columns become select-able columns in the query, as shown in the following code.
SELECT [AnyColumn] , [DataColumn] , [ValueColumn] FROM SomeTable ST UNPIVOT ( ValueColumn FOR DataColumn IN (UnPivotCol1, UnPivotCol2, UnPivotCol2) ) AS UNPVT
Applying this UNPIVOT syntax to our data above we yield the following query.
SELECT StudentID , Assignment , ASMT.Score FROM #ClassGrades CG UNPIVOT ( Score FOR Assignment IN (Assignment1, Assignment2, Assignment3, Assignment4) ) AS ASMT
This query returns the following normalized view of the data
Now that we have this data in a normalized result, applying SQL functions such as AVG() and SUM(), is relatively simple. Below is an updated version of the query using the AVG(), SUM(), MIN(), and MAX() functions grouping by StudentID.
SELECT StudentID , Average = AVG(ASMT.Score) , MaxScore = MAX(ASMT.Score) , MinScore = MIN(ASMT.Score) , Total = SUM(ASMT.Score) FROM #ClassGrades CG UNPIVOT ( Score FOR Assignment IN (Assignment1, Assignment2, Assignment3, Assignment4) ) AS ASMT GROUP BY StudentID
This query returns the following view of the data
As you can see the PIVOT and UNPIVOT statements are not overly complex and are definitely great TSQL tools for any DBA to keep in their back-pocket. Below is the entire script with test data included.
IF OBJECT_ID('tempdb..#ClassGrades') IS NOT NULL DROP TABLE #ClassGrades; CREATE TABLE #ClassGrades ( StudentID int PRIMARY KEY NOT NULL , Assignment1 int NULL , Assignment2 int NULL , Assignment3 int NULL , Assignment4 int NULL); INSERT INTO #ClassGrades (StudentID, Assignment1, Assignment2, Assignment3, Assignment4) VALUES (101, 90, 100, 95, 100), (102, 70, 65, 40, 80), (103, 100, 100, 100, 100); SELECT StudentID , Assignment , ASMT.Score FROM #ClassGrades CG UNPIVOT ( Score FOR Assignment IN (Assignment1, Assignment2, Assignment3, Assignment4) ) AS ASMT SELECT StudentID , Average = AVG(ASMT.Score) , MaxScore = MAX(ASMT.Score) , MinScore = MIN(ASMT.Score) , Total = SUM(ASMT.Score) FROM #ClassGrades CG UNPIVOT ( Score FOR Assignment IN (Assignment1, Assignment2, Assignment3, Assignment4) ) AS ASMT GROUP BY StudentID;