PIVOT and UNPIVOT in SQL Server

PIVOT and UNPIVOT are aggregate operators which are available from SQL Server version 2005 onwards.

PIVOT can be used to transform rows into columns while UNPIVOT can be used to transform columns into rows. We will see how to use PIVOT and UNPIVOT in the following examples

Let us create the following table which has sales details of products for each country.

create table #sales (country_name varchar(100),product_name varchar(100), 
sales_date datetime, sales_amount decimal(12,2))

insert into #sales (country_name,product_name,sales_date,sales_amount)
select 'India','Television','2012-01-10',35000 union all
select 'India','Mobile','2012-12-19',22000 union all
select 'India','Laptop','2012-04-11',62500 union all
select 'India','Laptop','2013-06-23',45000 union all
select 'India','Television','2012-03-20',45000 union all
select 'India','Television','2013-05-30',56000 union all
select 'India','Mobile','2013-02-22',71200 union all
select 'USA','Television','2012-02-20',3500 union all
select 'USA','Mobile','2012-11-01',2700 union all

select 'USA','Laptop','2012-08-19',6500 union all
select 'USA','Laptop','2013-06-23',5000 union all
select 'USA','Television','2012-02-12',4560 union all
select 'USA','Television','2013-06-30',5100 union all
select 'USA','Mobile','2013-006-06',2200 

SQL Server PIVOT table Example

Suppose you want to find out the total sales by each product and summarised for each year (ie columns are years). You can use the following code

SELECT product_name,[2012],[2013] from
(select year(sales_date) as sales_year,product_name,sales_amount FROM #sales)
as t 
PIVOT(SUM(sales_amount) 
      FOR sales_year IN ([2012],[2013])) AS pivot_table

If you want to group it by multiple columns, you can simply include that column in the query. For example the following query will do pivot group by country_name and product_name

SELECT country_name,product_name,[2012],[2013] from
(select year(sales_date) as sales_year, country_name, product_name, 
sales_amount FROM #sales) 
as t 
PIVOT(SUM(sales_amount) 
      FOR sales_year IN ([2012],[2013])) AS pivot_table

SQL Server UnPivot Table Example

To see how unpivot works, let us create the following table

create table #marks(student_name varchar(100), English smallint, 
Mathematics smallint, Tamil smallint, Science smallint)

insert into #marks(student_name , English, Mathematics, Tamil, Science)
select 'Sankar', 78,91,79,60 union all
select 'Nilesh', 81,90,66,89 union all
select 'Murugan', 94,88,72,90 

Suppose you want to transform the columns into rows i.e. subjects become rows, use the following query:

select student_name, subject_name, mark
from #marks s
unpivot
(
  mark
  for subject_name in (English,Mathematics, Tamil,Science)
) t;

Note : PIVOT in SQL Server basically produces a denormalised dataset whereas UNPIVOT produces normalised dataset.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan