-------------------------------------------------------------------
-- PIVOT demo from MSDN document
-------------------------------------------------------------------
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture,
StandardCost
FROM Production.Product
SELECT DaysToManufacture,
AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0],
[1],
[2],
[3],
[4]
FROM
(
SELECT DaysToManufacture,
StandardCost
FROM Production.Product
)AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
----------------------------------------------------------------
-- My Example for testing PIVOT
----------------------------------------------------------------
DECLARE @SourceTable TABLE
(
ID INT IDENTITY(1,1),
ProductName VARCHAR(20),
SaleMonth INT,
SalesCount INT
)
-- Inserting test data
INSERT INTO @SourceTable VALUES
('Bicycle',1,1),
('Shoes',2,2),
('Clothes',3,3),
('Books',4,4),
('Medicine',5,5),
('Drinks',6,6),
('Shoes',7,7),
('Books',1,2),
('Bicycle',1,3),
('Medicine',1,4),
('Clothes',1,5),
('Mobile Phone',1,6),
('Books',1,7),
('Medicine',1,8),
('Shoes',1,9),
('Bicycle',2,10)
-- Normal selection records
SELECT *
FROM @SourceTable
--------------------------------------------------------
/**
ID ProductName SaleMonth SalesCount
1 Bicycle 1 1
2 Shoes 2 2
3 Clothes 3 3
4 Books 4 4
5 Medicine 5 5
6 Drinks 6 6
7 Shoes 7 7
8 Books 1 2
9 Bicycle 1 3
10 Medicine 1 4
11 Clothes 1 5
12 Mobile Phone 1 6
13 Books 1 7
14 Medicine 1 8
15 Shoes 1 9
16 Bicycle 2 10
**/
--------------------------------------------------------
-- Columns List will be: Product Name, 1, 2, 3, 4, 5, 6
SELECT ProductName,
ISNULL([1],0) AS '1', -- Month
ISNULL([2],0) AS '2',
ISNULL([3],0) AS '3',
ISNULL([4],0) AS '4',
ISNULL([5],0) AS '5',
ISNULL([6],0) AS '6'
FROM (
-- The source records
SELECT ProductName,
SaleMonth,
SalesCount -- Will be sumed up.
FROM @SourceTable
)AS SourceTable
PIVOT
(
SUM(SalesCount)
FOR SaleMonth IN ([1],[2],[3],[4],[5],[6]) -- Month list from Column list
)AS PivotTable
----------------------------------------------------------------------------------
/**
ProductName 1 2 3 4 5 6
Bicycle 4 10 0 0 0 0
Books 9 0 0 4 0 0
Clothes 5 0 3 0 0 0
Drinks 0 0 0 0 0 6
Medicine 12 0 0 0 5 0
Mobile Phone 6 0 0 0 0 0
Shoes 9 2 0 0 0 0
**/
----------------------------------------------------------------------------------
-- Columns List will be: Sale Month, Bicycle, Shoes, Clothes, Books, Medicine
SELECT SaleMonth,
ISNULL([Bicycle],0) AS 'Bicycle',
ISNULL([Shoes],0) AS 'Shoes',
ISNULL([Clothes],0) AS 'Clothes',
ISNULL([Books],0) AS 'Books',
ISNULL([Medicine],0) AS 'Medicine'
FROM(
-- The source
SELECT ProductName,
SaleMonth,
SalesCount
FROM @SourceTable
) AS SourceTable
PIVOT
(
SUM(SalesCount)
FOR ProductName IN ([Bicycle],[Shoes],[Clothes],[Books],[Medicine])
) AS PivotTable
ORDER BY Shoes DESC -- Sort by the count of shoes.
--------------------------------------------------------------------------
/**
SaleMonth Bicycle Shoes Clothes Books Medicine
1 4 9 5 9 12
7 0 7 0 0 0
2 10 2 0 0 0
3 0 0 3 0 0
4 0 0 0 4 0
5 0 0 0 0 5
6 0 0 0 0 0
**/
--------------------------------------------------------------------------
No comments:
Post a Comment