How to use PIVOT in T-SQL queries?

Web has a lot of PIVOT examples, however all of them are based on fact tables. Regular relational database rarely has facts tables. Lets see how you can use PIVOT statement with regular database. First of all lets create sample Grocery database:

Grocery Database Diagram

Here is a script that can create this database:

CREATE DATABASE Grocery
GO
 
USE Grocery
 
CREATE TABLE Products
(
  ProductId INT IDENTITY(1,1) PRIMARY KEY,
  ProductName VARCHAR(50),
  Price MONEY
)
 
CREATE TABLE Stores
(
    StoreId INT IDENTITY(1,1) PRIMARY KEY,
    StoreName VARCHAR(50)
)
 
 
CREATE TABLE Orders
(
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    StoreId INT,
    OrderDate DATE,
    CONSTRAINT FK_Orders_Store FOREIGN KEY (StoreId) REFERENCES Stores(StoreId)
)
 
CREATE TABLE OrderProducts
(
    OrderId INT,
    ProductId INT,
    Qty INT,
    CONSTRAINT PK_OrderId_ProductId PRIMARY KEY (OrderId,ProductId),
    CONSTRAINT FK_OrderProducts_Products FOREIGN KEY (ProductId) REFERENCES Products(ProductId),
    CONSTRAINT FK_OrderProducts_Orders FOREIGN KEY (OrderId) REFERENCES Orders(OrderId)
)

 

Lets populate those tables:

 
INSERT INTO Products
(ProductName, Price)
VALUES  
('Milk', 2.99),
('Bread',1.99),
('Tomato',0.99),
('Grape',1.99)
 
INSERT INTO Stores
(StoreName )
VALUES  
('Jewel'),
('Dominicks'),
('Walmart')
 
INSERT INTO Orders
(StoreId, OrderDate )
VALUES  
(1,'1/11/2011'),
(1,'1/14/2011'),
(2,'2/05/2011'),
(3,'3/17/2011'),
(3,'3/29/2011'),
(3,'4/02/2011')
 
 
INSERT INTO OrderProducts
( OrderId, ProductId, Qty )
VALUES 
(1,2,2),
(1,1,1),
(2,3,1),
(3,4,3),
(3,1,2),
(3,3,1),
(4,3,2),
(4,4,2),
(5,1,1),
(5,3,1),
(6,1,1),
(6,2,3),
(6,4,1)

 

Products:

ProductId ProductName Price
1 Milk 2.99
2 Bread 1.99
3 Tomato 0.99
4 Grape 1.99

 

Stores:

StoreId StoreName
1 Jewel
2 Dominicks
3 Walmart

 

Orders:

OrderId StoreId OrderDate
1 1 2011-01-11
2 1 2011-01-14
3 2 2011-02-05
4 3 2011-03-17
5 3 2011-03-29
6 3 2011-04-02

 

OrderProducts:

OrderId ProductId Qty
1 1 1
1 2 2
2 3 1
3 1 2
3 3 1
3 4 3
4 3 2
4 4 2
5 1 1
5 3 1
6 1 1
6 2 3
6 4 1

 

Now our sample relation database is ready for some pivoting. Lets say we want to know what products are sold by what store. Lets pull needed data using regular query:

SELECT 
    s.StoreName,p.ProductName, op.Qty * p.Price AS Totals 
FROM 
    Products p INNER JOIN 
    OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
    Orders o ON op.OrderId = o.OrderId INNER JOIN 
    Stores s ON o.StoreId = s.StoreId

 

Results:

StoreName ProductName Totals
Jewel Milk 2.99
Jewel Bread 3.98
Jewel Tomato 0.99
Dominicks Milk 5.98
Dominicks Tomato 0.99
Dominicks Grape 5.97
Walmart Tomato 1.98
Walmart Grape 3.98
Walmart Milk 2.99
Walmart Tomato 0.99
Walmart Milk 2.99
Walmart Bread 5.97
Walmart Grape 1.99

 

Those result rows look like a fact table. Lets apply PIVOT to them:

SELECT * FROM
(
    SELECT 
        s.StoreName,p.ProductName, op.Qty * p.Price AS Totals 
    FROM 
        Products p INNER JOIN 
        OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
        Orders o ON op.OrderId = o.OrderId INNER JOIN 
        Stores s ON o.StoreId = s.StoreId
) AS Source        
PIVOT
(
    SUM(Totals)
    FOR ProductName IN ([Bread],[Milk],[Tomato],[Grape])    
) AS p

 

PIVOT results:

StoreName Bread Milk Tomato Grape
Dominicks NULL 5.98 0.99 5.97
Jewel 3.98 2.99 0.99 NULL
Walmart 5.97 5.98 2.97 5.97

 

Lets do another one: products sales by months.

SELECT 
    p.ProductName, DATENAME(m,o.OrderDate) AS [Month], op.Qty * p.Price AS Totals 
FROM 
    Products p INNER JOIN 
    OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
    Orders o ON op.OrderId = o.OrderId INNER JOIN 
    Stores s ON o.StoreId = s.StoreId

 

Results:

ProductName Month Totals
Milk January 2.99
Bread January 3.98
Tomato January 0.99
Milk February 5.98
Tomato February 0.99
Grape February 5.97
Tomato March 1.98
Grape March 3.98
Milk March 2.99
Tomato March 0.99
Milk April 2.99
Bread April 5.97
Grape April 1.99

 

PIVOT version:

SELECT * FROM
(
    SELECT 
        p.ProductName, DATENAME(m,o.OrderDate) AS [Month], op.Qty * p.Price AS Totals 
    FROM 
        Products p INNER JOIN 
        OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
        Orders o ON op.OrderId = o.OrderId INNER JOIN 
        Stores s ON o.StoreId = s.StoreId
) AS Source        
PIVOT
(
    SUM(Totals)
    FOR [Month] IN ([January],[February],[March],[April])    
) AS p

 

PIVOT results:

ProductName January February March April
Bread 3.98 NULL NULL 5.97
Grape NULL 5.97 3.98 1.99
Milk 2.99 5.98 2.99 2.99
Tomato 0.99 0.99 2.97 NULL

 

And last one lets do stores sales by months:

SELECT 
    s.StoreName,DATENAME(m,o.OrderDate) AS [Month], op.Qty * p.Price AS Totals 
FROM 
    Products p INNER JOIN 
    OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
    Orders o ON op.OrderId = o.OrderId INNER JOIN 
    Stores s ON o.StoreId = s.StoreId

 

Results:

StoreName Month Totals
Jewel January 2.99
Jewel January 3.98
Jewel January 0.99
Dominicks February 5.98
Dominicks February 0.99
Dominicks February 5.97
Walmart March 1.98
Walmart March 3.98
Walmart March 2.99
Walmart March 0.99
Walmart April 2.99
Walmart April 5.97
Walmart April 1.99

 

PIVOT version:

SELECT * FROM
(
    SELECT 
        s.StoreName,DATENAME(m,o.OrderDate) AS [Month], op.Qty * p.Price AS Totals 
    FROM 
        Products p INNER JOIN 
        OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
        Orders o ON op.OrderId = o.OrderId INNER JOIN 
        Stores s ON o.StoreId = s.StoreId
) AS Source        
PIVOT
(
    SUM(Totals)
    FOR [Month] IN ([January],[February],[March],[April])    
) AS p

 

PIVOT results:

StoreName January February March April
Dominicks NULL 12.94 NULL NULL
Jewel 7.96 NULL NULL NULL
Walmart NULL NULL 9.94 10.95

Posted on Tuesday, March 27, 2012 by | Add Comment

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5