PIVOT 和 UNPIVOT

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

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

--------------------------------------------------------------------- PIVOT demo from MSDN document-------------------------------------------------------------------USE AdventureWorks2008R2 ;GO SELECT DaysToManufacture,    StandardCostFROM Production.Product SELECT DaysToManufacture,    AVG(StandardCost) AS AverageCostFROM Production.ProductGROUP BY DaysToManufacture; -- Pivot table with one row and five columnsSELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   [0],   [1],   [2],   [3],   [4]FROM(SELECT DaysToManufacture,    StandardCostFROM Production.Product)AS SourceTablePIVOT(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 dataINSERT 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 recordsSELECT *FROM @SourceTable--------------------------------------------------------/**ID    ProductName    SaleMonth    SalesCount1    Bicycle    1    12    Shoes    2    23    Clothes    3    34    Books    4    45    Medicine    5    56    Drinks    6    67    Shoes    7    78    Books    1    29    Bicycle    1    310    Medicine    1    411    Clothes    1    512    Mobile Phone    1    613    Books    1    714    Medicine    1    815    Shoes    1    916    Bicycle    2    10**/-------------------------------------------------------- -- Columns List will be: Product Name, 1, 2, 3, 4, 5, 6SELECT 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 SourceTablePIVOT     (        SUM(SalesCount)        FOR SaleMonth IN ([1],[2],[3],[4],[5],[6])  -- Month list from Column list     )AS PivotTable----------------------------------------------------------------------------------/**ProductName    1    2    3    4    5    6Bicycle    4    10    0    0    0    0Books    9    0    0    4    0    0Clothes    5    0    3    0    0    0Drinks    0    0    0    0    0    6Medicine    12    0    0    0    5    0Mobile Phone    6    0    0    0    0    0Shoes    9    2    0    0    0    0**/---------------------------------------------------------------------------------- -- Columns List will be: Sale Month, Bicycle, Shoes, Clothes, Books, MedicineSELECT 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 SourceTablePIVOT   (      SUM(SalesCount)      FOR ProductName IN ([Bicycle],[Shoes],[Clothes],[Books],[Medicine])   ) AS PivotTableORDER BY Shoes DESC -- Sort by the count of shoes.--------------------------------------------------------------------------/**SaleMonth    Bicycle    Shoes    Clothes    Books    Medicine1    4    9    5    9    127    0    7    0    0    02    10    2    0    0    03    0    0    3    0    04    0    0    0    4    05    0    0    0    0    56    0    0    0    0    0**/