The PIVOT operator allows you to rotate, or pivot, data between columns and rows, and performs aggregations where they are required on any remaining column values. The UNPIVOT operator performs the reverse operation of PIVOT, by rotating columns into rows.
Note: PIVOT and UNPIVOT operators are available in SQL Server 2005 and onwards.
Here’s an example of using the UNPIVOT operator to convert repeating columns of a table, into a more normalized form
Sample Denormalized Table
CREATE TABLE #Student
StudentID int ,
INSERT INTO #Student VALUES (1, 5.6, 7.3, 4.2)
INSERT INTO #Student VALUES (2, 4.8, 7.9, 6.5)
INSERT INTO #Student VALUES (3, 6.8, 6.6, 8.9)
INSERT INTO #Student VALUES (4, 8.2, 9.3, 9.1)
INSERT INTO #Student VALUES (5, 6.2, 5.4, 4.4)
SELECT * FROM #Student
Let us know normalize this table using an UNPIVOT operator
Here’s the query to try out
SELECT StudentID, MarksNo, MarksRecd
Marks1, Marks2, Marks3
FROM #Student) stu
(MarksRecd FOR MarksNo IN (Marks1, Marks2, Marks3)
) AS mrks
As you can see, we have declared two new columns here – MarksNo and MarksRecd. Here the column that will contain the column values (the ones that will be rotated like Marks1, Marks2,...) will be called MarksNo , and the column that will hold the values that currently reside under the rotated columns, will be called MarksRecd. This rotation of columns into rows is carried out by the UNPIVOT operator.