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:
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 |