Pivot In SQL Server

In this blog, you will learn, how to use Pivot table in SQL Server, using example.

What is pivot? 

Pivot is one of the new relational operators, introduced in SQL Server. It provides an easy mechanism to transform rows into the columns. This allows you to turn data rows into the columns and is used to create repors.

 

Step 1: Create a table to describe the use of Pivot in SQL Server.

create table Product
(
 ProductId int identity(1,1)primary key,
 ManufactoringYear int,
 ProductName varchar(20),
 ProductPrice money,
 ProductQTY int
)

Step 2: Insert value into Product table to show the output to describe Pivot:

insert Product values(2000,'Laptop',23000,4)
insert Product values(2001,'Laptop',21000,7)
insert Product values(2002,'Laptop',30000,3)
insert Product values(2003,'Laptop',27000,2)
insert Product values(2000,'Printer',5000,8)
insert Product values(2001,'Printer',4000,6)
insert Product values(2002,'Printer',4500,5)
insert Product values(2003,'Printer',6000,1)
insert Product values(2000,'RAM',2000,12)
insert Product values(2001,'RAM',2300,10)
insert Product values(2002,'RAM',30000,9)
insert Product values(2003,'RAM',25000,4)

Step 3: Write a query to select the date to verify the records in Product table.

select * from Product

Exampe 1: Display the product name and total number of products in every year.

select ProductName,[2000]AS 'FirstYear(2000)',[2001]AS 'SecondYear(2001)',[2002]AS 'ThirdYear(2002)',[2003]AS 'FourthYear(2004)' from
(select ProductName,ManufactoringYear,ProductQTY from Product) pvt
pivot
(
sum(ProductQTY)for ManufactoringYear IN([2000],[2001],[2002],[2003])
)as p1

Note: In this example, ManufactoringYear is converted as a column to display the total number of products in every ManufacturingYear.

As standard table ManufacturingYear, column contains value as 2000, 2001, 2002, 2003, 2004 but using pivot, this value converts as a form of column to describe the yearly product details.

Example 2: Display the product count for every year.

select ManufactoringYear,Laptop,Printer,RAM from
(select ManufactoringYear,ProductName,ProductQTY from Product) pvt
pivot
(
sum(ProductQTY)for ProductName IN(Laptop,Printer,RAM)
)as p1

Note: In this example, the product name is converted as a number of a column to display the total number of products in ManufacturingYear.

As a standard table, productname column contains value as Laptop, Printer, RAM but using pivot, this value is converted as a form of column to describe the product name and product details.

 
Example 3: Display the total number of products. 

select Laptop,Printer,RAM from
(select ProductName,ProductQTY from Product) pvt
pivot
(
sum(ProductQTY)for ProductName IN(Laptop,Printer,RAM)
)as p1

Note: In this example, the product name is converted as a number of columns to display the total number of products.

 

A standard table productname column contains value as Laptop, Printer, and RAM, but using pivot, this value is converted as a form of column to describe the product name and product details.