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….!!!