The usage of PIVOT keyword

In SQL Server we can transform a horizontal rows to be vertically represented by using Pivot Keyword

Run this query to have sample data

CREATE TABLE OrderItems(Order_No INT, ProductName VARCHAR(255), TotalQty INT)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (112, ‘Sprite’, 5)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (112, ‘Coke’, 7)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (112, ‘Lemonade’, 2)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (113, ‘Apple Juice’, 8 )

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (113, ‘Diet Coke’, 2)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (114, ‘Coke’, 15)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (114, ‘Lemonade’, 13)

GO

Running simple select from OrderItems will give you this table

Now we want to look it statistically – maybe for reporting purpose. Run the query below

SELECT Order_No, Sprite, Coke, Lemonade, [Apple Juice], [Diet Coke]

FROM (

SELECT Order_No, ProductName, TotalQty

FROM OrderItems) ItemList

PIVOT (SUM(TotalQty) FOR ProductName IN (Sprite, Coke, Lemonade, [Apple Juice], [Diet Coke])) AS PivotResult

ORDER BY Order_No

GO

It will give you this result set