KARPACH

WEB DEVELOPER BLOG

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. Let’s see how you can use PIVOT statement with a regular database. First of all, let’s create a 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)
)

Let’s 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. Let’s say we want to know what products are sold by what store. Let’s pull needed data using a 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. Let’s 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

Let’s do another one: product sales by month.

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 the last one let’s 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 March 27, 2012 by