MS SQL Server

How to Use SQL Server Unpivot

The pivot operator in SQL Server allows you to convert rows in a table into column data. The unpivot operator does the opposite of pivot and turns column data into rows.This guide will give a quick walkthrough of using the unpivot operator in SQL Server.

The Basics

The syntax for the unpivot operator is as:

selectcol_listfromunpivot [value_column]
for [pivot_columnincolumn_list
  1. pivot_column – column holding values you wish to rotate.
  2. value_column – column holding values existing under the column to rotate.

SQL Server Unpivot Example

Suppose we have a table with product information as shown:

createtableunpiv(
    id intidentity(1,1) notnullprimary key,
    product_namevarchar(50),
    price money,
    quantity int
);
-- insert sample data
insertintounpiv(product_name, price, quantity)
values ('product1', $9.99, 10),
       ('product2', $11.90, 2),
       ('product3', $114.00, 3),
       ('product4', $33.2, 4),
       ('product5', $221.20, 5);

We can apply the pivot operator to the table above as:

selectproduct_name, price, quantity
from
(
    selectproduct_name, product1, product2 fromunpiv
) as tb1
unpivot
(
    Quantity forproduct_nameIN (product1, product2)
) asunpivt;

The query above should pivot the columns into a single column.

Conclusion

This short guide discussed the syntax and usage of the unpivot operator in the SQL Server and provided an example to highlight the function of the unpivot operator. We hope you found this article helpful. Check out more Linux Hint articles for more tips and information on SQL Server tutorial.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list