Fortunately I have done, leaving a foundation here. The simplest description of PIVOT and Unpivot-row-column, column-Change!!!
SSIS also have such a control, the effect is the same.
---------------------------------------------------------------------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 (St
Andardcost) 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 ( -), 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,Ten)--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 Medicine 1 4 Clothes 1 5 Mobile Phone 1 6 Books 1 7 medicine 1 8 Shoes 1 9 Bicycle 2 **/----------------------------------------------------------C Olumns List would 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],0As ' 6 ' from (--the source records SELECT ProductName, Salemonth, Salesco
UNT--would be sumed up. From @SourceTable) as SourceTable PIVOT (SUM (Salescount) to Salemonth in ([1],[2],[3],[4],[5], [6])--Month list from Column list) as PivotTable----------------------------------------------------------------- -----------------/** ProductName 1 2 3 4 5 6 Bicycle 4 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 would be:sale Month, Bicycle, Shoes, Clothes, book s, medicine SELECT Salemonth, ISNULL ([Bicycle],0) as ' Bicycle ', ISNULL ([Shoes],0) as ' Shoes ', ISNULL ([Clothes],0As ' Clothes ', ISNULL ([books],0As ' books ', ISNULL ([medicine],0As ' medicine ' from (--The source SELECT ProductName, Salemonth, Salescount From @SourceTable) as SourceTable PIVOT (SUM (Salescount) to ProductName in ([Bicycle],[shoes],[cloth Es],[books],[medicine])) as PivotTable order by Shoes DESC--Sort by the count of Shoes.