容易遗忘的一些小代码之 PIVOT 和 UNPIVOT

这个代码以前也经常使用, 不过一段时间之后,每每有人问起如何使用或者要求帮忙检查出错问题时,就要回头再看看这个例子.

还好自己搞过,留个底子在这里. 对 PIVOT 和 UNPIVOT 最简单描述 - 行转列,列转行!!!

SSIS 中也有这样的一个控件,实现的效果也是一样的.

-------------------------------------------------------------------
-- 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
**/
--------------------------------------------------------------------------