January 23, 2011

UNPIVOT example in SQL Server




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 ,
Marks1 float,
Marks2 float,
Marks3 float
)

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

OUTPUT

Unpivot Example

Let us know normalize this table using an UNPIVOT operator

Here’s the query to try out

SELECT StudentID, MarksNo, MarksRecd
FROM
(SELECT StudentID,
Marks1, Marks2, Marks3
FROM #Student) stu
UNPIVOT
(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.

OUTPUT


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "UNPIVOT example in SQL Server"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions