How to Perform Pivot Operation 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!!!