Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted June 1, 2015

Converting Rows to Columns (PIVOT) and Columns to Rows (UNPIVOT) in SQL Server

By Arshad Ali

Introduction

In my last article “Converting Comma Separated Value to Rows and Vice Versa in SQL Server”, I talked about how you can convert comma separated (or separated with some other character) values in a single column into rows and vice versa. In this article, I demonstrate how you can convert rows values into columns values (PIVOT) and columns values into rows values (UNPIVOT) in SQL Server.

Converting Rows to Columns – PIVOT

SQL Server has a PIVOT relational operator to turn the unique values of a specified column from multiple rows into multiple column values in the output (cross-tab), effectively rotating a table. It also allows performing aggregations, wherever required, for column values that are expected in the final output. The basic syntax for a PIVOT relational operator looks like this:

SELECT <<ColumnNames>> 
FROM <<TableName>> 
PIVOT
 (
   AggregateFunction(<<ColumnToBeAggregated>>)
   FOR PivotColumn IN (<<PivotColumnValues>>)
 ) AS <<Alias>> 

With the script below, let’s create a table and load some data into it. As you can see in the image below, it has sales information for some countries for a couple of years. Also, if you notice, for each country and for each year there is a separate row.

CREATE TABLE [dbo].[PivotExample](
       [Country]   [nvarchar](50)   NULL,
       [Year]   [smallint] NOT NULL,
       [SalesAmount]   [money] NULL
)
GO
 
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Australia', 2005, 1309047.1978)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Germany', 2006, 521230.8475)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'United States',   2007, 2838512.3550)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'France', 2008, 922179.0400)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Australia', 2007, 3033784.2131)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'France', 2005, 180571.6920)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'United Kingdom',   2006, 591586.8540)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Canada', 2006, 621602.3823)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'United Kingdom',   2005, 291590.5194)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'United States',   2005, 1100549.4498)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Canada', 2007, 535784.4624)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'France', 2007, 1026324.9692)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Germany', 2007, 1058405.7305)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Australia', 2006, 2154284.8835)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'United Kingdom',   2008, 1210286.2700)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'United States',   2008, 3324031.1600)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Germany', 2008, 1076890.7700)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'United Kingdom',   2007, 1298248.5675)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Australia', 2008, 2563884.2900)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Canada', 2005, 146829.8074)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Germany', 2005, 237784.9902)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'Canada', 2008, 673628.2100)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'United States',   2006, 2126696.5460)
INSERT   [dbo].[PivotExample]   ([Country],   [Year], [SalesAmount])   VALUES (N'France', 2006, 514942.0131)
GO
 
SELECT * FROM [dbo].[PivotExample] ORDER   BY Country
GO

Results Screenshot

 

Now by using the PIVOT operator, we will convert row values into column values with the script given below and the results as shown in the image below.

Though we have used the SUM aggregation function, in this case there is no summation, as there is only one row for each unique combination for country and year. Please note the use of brackets for pivot column values; these are required.

SELECT   [Country], [2005],   [2006], [2007],   [2008]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN ([2005], [2006], [2007], [2008])
) AS P

Results Screenshot

Bringing Dynamism to the PIVOT

If you notice in the above script, we have provided values (2005, 2006, 2007 and 2008) for pivot columns as these values are available in the original datasets. But what if some additional values are expected to come in the future, for example 2009 and 2010, etc. for the pivot column?

In that case, you can still use the pivot column values, which are expected to come (or which are still not available in the original dataset) in the future though you will see NULL for its values. The script below shows this scenario and the image below shows NULLs for the years 2009 and 2010 as there is no data for these years.

SELECT   [Country], [2005],   [2006], [2007],   [2008], [2009],   [2010]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN ([2005], [2006], [2007], [2008], [2009], [2010])
) AS P

Results Screenshot

The above discussed approach works fine if you already know all of the possible values for the pivot column, but what if you don’t?

In that case, you can write a dynamic query to first grab all the unique values for the pivot column at runtime and then a write dynamic query to execute it with the pivot query as shown below:

--Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
 
--Get unique values of pivot column  
SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(Year)
FROM (SELECT DISTINCT Year FROM [dbo].[PivotExample]) AS PivotExample
 
SELECT   @PivotColumns
 
--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N'SELECT Country, ' +   @PivotColumns + '
    FROM [dbo].[PivotExample] 
    PIVOT( SUM(SalesAmount) 
          FOR Year IN (' + @PivotColumns + ')) AS P'
 
SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

 

Results Screenshot

Converting Columns to Rows – UNPIVOT

UNPIVOT is another relational operator in SQL Server that performs almost the reverse operation of PIVOT, by rotating column values into rows values. Let me demonstrate this with an example; lets create a table with pivoted data from the previous query with the script below. The image below shows data of the newly created table with pivoted data.

SELECT   [Country], [2005],   [2006], [2007],   [2008] 
INTO   [dbo].[UnpivotExample]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN ([2005], [2006], [2007], [2008])
) AS P
GO
SELECT * FROM [dbo].[UnpivotExample] ORDER   BY Country
GO

Results Screenshot

To rotate column values into row values, we will now use the UNPIVOT operator as shown below. The image below shows rotated data:

SELECT   Country, Year, SalesAmount 
FROM [dbo].[UnpivotExample]
UNPIVOT
(
       SalesAmount
       FOR [Year] IN ([2005], [2006], [2007], [2008])
) AS P

Results Screenshot

If you refer back, I said UNPIVOT is almost the reverse of the PIVOT operator; this means it might or might not be exactly the same. The reason is, Pivot performs aggregation while rotating row values into column values and might merge possible multiple row values into single column value in the output. For example, consider for a given country and year there are two values, say 5000 and 6000. Now when you pivot it, the output will have 11000 as the column value if you have SUM as the aggregation function. Later if you want to unpivot it back, you will get 11000 not the bi-furcated values (5000 and 6000 as original). Having said that, we can say if the pivoted values are aggregated values, it will not be possible to get the original data back.

Conclusion

In this article, I demonstrated how you can convert row values into column values (PIVOT) and column values into row values (UNPIVOT) in SQL Server. I also talked about writing a dynamic query to write a dynamic pivot query in which possible pivot column values are determined at runtime.

Resources

Using PIVOT and UNPIVOT

Converting Comma Separated Value to Rows and Vice Versa in SQL Server

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM