This post has already been read 42 times!

-- CREATE TABLE & INSERT SAMPLES
-- CREATE TABLE & INSERT SAMPLES

Create Table tblProductSales 
( 
SalesAgent nvarchar(10), 
Country nvarchar(10), 
SalesAmount int 
) 

Insert into tblProductSales values('David','India',960) 
Insert into tblProductSales values('David','US',520) 
Insert into tblProductSales values('John','India',970) 
Insert into tblProductSales values('John','US',540) 

 

  • PIVOT Operator
PIVOT operator
-- PIVOT OPERATOR to turn ROWS into COLUMNS

SELECT SalesAgent, India, US 
FROM tblProductSales 
PIVOT 
( 
SUM(SalesAmount) FOR Country IN (India, US) 
) AS PivotTable 

  • UNPIVOT Operator
UNPIVOT operator
-- Now let's use the UNPIVOT operator to reverse what PIVOT operator has done

SELECT SalesAgent, Country, SalesAmount 
FROM 
	(
	SELECT SalesAgent, India, US FROM tblProductSales 
	PIVOT 
	( 
	SUM(SalesAmount) FOR Country IN (India, US) 
	) AS PivotTable
) P 
UNPIVOT 
( 
	SalesAmount FOR Country IN (India, US) 
) 
AS UnpivotTable

 

 

Leave a Reply

Post Navigation