Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

How to Perform Pivot Operation in T-SQL..?

, 16 Jun 2013
Rate this:
Please Sign up or sign in to vote.
This tip discusses how to perform pivot operations in T-SQL.

Introduction

Microsoft SQL Server has introduced the PIVOT and UNPIVOT commands as enhancements to T-SQL with the release of Microsoft SQL Server 2005.

In Microsoft SQL Server 2008, we can still use the PIVOT command and UNPIVOT commands to build and use pivot tables in SQL.

T-SQL, Pivot and Unpivot statements will transform and rotate a tabular data into another table value data in SQL.

It is used to generate cross tabulation reports to summarize data as it creates a more easily understandable data in a user friendly format.

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, i.e., it rotates rows to columns and aggregations where they are required on any remaining column values that are wanted in the final output.

Syntax

SELECT 	<non-pivoted column>,
    		[pivoted column] As <column name>,
    		[pivoted column] As <column name>,
  		...
    		[pivoted column] As <column name>
FROM
   	 (<SELECT query that produces the data>) As <alias for the source query>
                    PIVOT
                    (
       			<aggregation function>(column)
                              FOR
                              [<column that contains the values that will become column headers>]
                              IN ( [pivoted column], [pivoted column],
                              ... [pivoted column])
                    ) As <alias for the pivot table>
[ORDER BY clause]

Example SQL server 2008

CREATE TABLE Customer_Order
(
	Customer VARCHAR(10),
	MONTH INTEGER, 
	YEAR INTEGER, 
	Purchase_Made INTEGER
)    

 INSERT INTO Customer_Order VALUES('Bhushan',1,2012, 11)  
 INSERT INTO Customer_Order VALUES('Bhushan',2,2012, 8)  
 INSERT INTO Customer_Order VALUES('Bhushan',1,2012, 10)  
 INSERT INTO Customer_Order VALUES('Bhushan',4,2012, 2)  
 INSERT INTO Customer_Order VALUES('Bhushan',5,2012, 7)  
 INSERT INTO Customer_Order VALUES('Bhushan',2,2012, 1)  
 INSERT INTO Customer_Order VALUES('Bhushan',1,2012, 4)  
 INSERT INTO Customer_Order VALUES('Bhushan',6,2012, 5)  
 INSERT INTO Customer_Order VALUES('Bhushan',8,2012, 8 )  
 INSERT INTO Customer_Order VALUES('Bhushan',3,2013, 5)  
 INSERT INTO Customer_Order VALUES('Bhushan',5,2013, 7)  
 INSERT INTO Customer_Order VALUES('Bhushan',12,2013, 5)  
 INSERT INTO Customer_Order VALUES('Bhushan',11,2013, 4)  
 INSERT INTO Customer_Order VALUES('Bhushan',1,2013, 7)  
 INSERT INTO Customer_Order VALUES('Bhushan',5,2013, 5)  
 INSERT INTO Customer_Order VALUES('Ash',2,2012, 6)  
 INSERT INTO Customer_Order VALUES('Ash',4,2012, 7)  
 INSERT INTO Customer_Order VALUES('Ash',2,2012, 4)  
 INSERT INTO Customer_Order VALUES('Ash',3,2012, 5)  
 INSERT INTO Customer_Order VALUES('Ash',5,2012, 7)  
 INSERT INTO Customer_Order VALUES('Ash',12,2012, 2)  
 INSERT INTO Customer_Order VALUES('Ash',11,2012, 4)  
 INSERT INTO Customer_Order VALUES('Ash',1,2012, 9)  
 INSERT INTO Customer_Order VALUES('Ash',5,2012, 4)  
 INSERT INTO Customer_Order VALUES('Ash',3,2012, 5)  
 INSERT INTO Customer_Order VALUES('Ash',4,2013, 7)  
 INSERT INTO Customer_Order VALUES('Ash',1,2013, 1)  
 INSERT INTO Customer_Order VALUES('Ash',4,2013, 4)  
 INSERT INTO Customer_Order VALUES('Ash',2,2013, 9)  
 INSERT INTO Customer_Order VALUES('Ash',5,2013, 4)    
 INSERT INTO Customer_Order VALUES('Hershal',1,2012, 5)  
 INSERT INTO Customer_Order VALUES('Hershal',3,2012, 6)  
 INSERT INTO Customer_Order VALUES('Hershal',5,2012, 8 )  
 INSERT INTO Customer_Order VALUES('Hershal',12,2012, 3)  
 INSERT INTO Customer_Order VALUES('Hershal',9,2012, 5)  
 INSERT INTO Customer_Order VALUES('Hershal',5,2012, 3)  
 INSERT INTO Customer_Order VALUES('Hershal',1,2012, 5)  
 INSERT INTO Customer_Order VALUES('Hershal',4,2012, 3)  
 INSERT INTO Customer_Order VALUES('Hershal',3,2012, 9)  
 INSERT INTO Customer_Order VALUES('Hershal',3,2013, 5)  
 INSERT INTO Customer_Order VALUES('Hershal',5,2013, 7)  
 INSERT INTO Customer_Order VALUES('Hershal',12,2013, 1)  
 INSERT INTO Customer_Order VALUES('Hershal',11,2013, 4)  
 INSERT INTO Customer_Order VALUES('Hershal',1,2013, 9)  
 INSERT INTO Customer_Order VALUES('Hershal',5,2013, 4) 

T-SQL Script for Pivot

SELECT		*  
FROM
	(SELECT	Customer,
			CAST(YEAR As VARCHAR(4)) + ' ' + _
			CONVERT(VARCHAR(3), DATEADD(M, MONTH, -1), 107) As MONTHS,  
			Purchase_Made  
	 FROM
			Customer_Order
	 )P
		PIVOT
		(  
		SUM(Purchase_Made)  
		FOR MONTHS IN ([2012 Apr], [2012 May], [2012 Jun], [2012 Jul], [2012 Aug])
		)AS PVT  

DROP TABLE Customer_Order

Output

Done!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bhushan W. Juare
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 16 Jun 2013
Article Copyright 2013 by Bhushan W. Juare
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid