Introduction
Pivots are used to convert row data to column data
Demo
Lets create a table first
CREATE Table Sales
(SalesPerson varchar(50),
Region varchar(20),
Sale INT)
Insert Records in Sales table
INSERT INTO Sales
Values ('Nitin','Europe',200),('Sara','Europe',300),('Calvin','Europe',250),
('Nitin','US',500),('Sara','US',400),('Calvin','US',150),
('Nitin','INDIA',250),('Sara','INDIA',100),('Calvin','INDIA',150),
('Nitin','Europe',300),('Sara','Europe',500),('Calvin','Europe',300),
('Nitin','US',400),('Sara','US',600),('Calvin','US',750),
('Nitin','INDIA',450),('Sara','INDIA',150),('Calvin','INDIA',100)
Now we want the region wise total sales of each sales person. We have to pivot Europe,US and India as column
Select SalesPerson,INDIA,Europe,US
From Sales
PIVOT
(SUM(Sale)
FOR Region
IN (INDIA,Europe,US)
)
AS PIVOTTABLE
It is as easy as this but what if table has some columns that we don’t need in the output. Let’s go ahead and
add ID column in the table
DROP TABLE Sales
CREATE Table Sales
(ID INT IDENTITY(1,1),
SalesPerson varchar(50),
Region varchar(20),
Sale INT)
Insert the records in table
INSERT INTO Sales
Values ('Nitin','Europe',200),('Sara','Europe',300),('Calvin','Europe',250),
('Nitin','US',500),('Sara','US',400),('Calvin','US',150),
('Nitin','INDIA',250),('Sara','INDIA',100),('Calvin','INDIA',150),
('Nitin','Europe',300),('Sara','Europe',500),('Calvin','Europe',300),
('Nitin','US',400),('Sara','US',600),('Calvin','US',750),
('Nitin','INDIA',450),('Sara','INDIA',150),('Calvin','INDIA',100)
Run the same query again
Select SalesPerson,INDIA,Europe,US
From Sales
PIVOT
(SUM(Sale)
FOR Region
IN (INDIA,Europe,US)
)
AS PIVOTTABLE
Now, This is not the expected result. We have got 18 rows this time because we have extra column ‘ID’ in the table. We will have to re-write the query as below to get the expected result
Select SalesPerson,INDIA,Europe,US
From
(Select SalesPerson,Region,Sale from Sales) as Sales
PIVOT
(SUM(Sale)
FOR Region
IN (INDIA,Europe,US)
)
AS PIVOTTABLE
And we have the expected result.
Happy Learning….!!!
Very informative
Informative and crisp clear because of examples.