日韩黄色在线,欧美中文在线观看,日韩欧美在线观看

??????? PIVOT，UNPIVOT運算符是SQL server 2005支持的新功能之一，主要用來實現(xiàn)行到列的轉(zhuǎn)換。本文主要介紹PIVOT運算符的操作，以及如何實現(xiàn)動態(tài)PIVOT的行列轉(zhuǎn)換。

?????? 關(guān)于UNPIVOT及SQL server 2000下的行列轉(zhuǎn)換請參照本人的其它文章。

一、PIVOT的語法

SELECT [non-pivoted column], -- optional [additional non-pivoted columns], -- optional [first pivoted column], [additional pivoted columns] FROM ( SELECT query producing sql data for pivot -- select pivot columns as dimensions and -- value columns as measures from sql tables ) AS TableAlias PIVOT ( <aggregation function>(column for aggregation or measure column) -- MIN,MAX,SUM,etc FOR [] IN ( [first pivoted column], ..., [last pivoted column] ) ) AS PivotTableAlias ORDER BY clause – optional
二、PIVOT的使用例子

1. 靜態(tài)PIVOT的用法
?????? 為演示，從NorthWind數(shù)據(jù)庫中提取一些記錄生成新的Orders表，然后使用PIVOT將行轉(zhuǎn)換到列。

USE tempdb

GO

SELECT YEAR(OrderDate) AS [Year]

       ,CustomerID 

       ,od.Quantity

INTO dbo.Orders       

FROM NorthWind..Orders AS o

    JOIN NorthWind..[Order Details] AS od

        ON o.OrderID = od.OrderID

WHERE o.CustomerID IN ('BONAP','BOTTM','ANTON')

SELECT CustomerID

	,[1996],[1997],[1998]

FROM dbo.Orders

PIVOT (

	   SUM(Quantity)

	   FOR [Year] IN ([1996],[1997],[1998])

   )x

/*			   

TSQL中pivot的結(jié)構(gòu)：

    ●  用于生成pivot數(shù)據(jù)源的源表，作為一個輸入表

    ●  pivot表

    ●  聚合列及透視列的選擇



TSQL中pivot的實現(xiàn)：

1->上例中Orders表相當(dāng)于是一個輸入表。包含了CustomerID,[Year],Quantity 三個列。

   Year是透視列，用于生成維度。

   pivot首先將聚合列之外的列進行分組，并對其實現(xiàn)聚合。本列中則是對聚合列Quantity之外的列先實現(xiàn)分組，

   即對CustomerID，Year進行分組，并對其Quantity實現(xiàn)聚合，相當(dāng)于先做如下處理：

*/  			   

SELECT CustomerID

       ,[Year]

       ,SUM(Quantity) AS Total

FROM dbo.Orders

GROUP BY CustomerID

       ,[Year]

ORDER BY CustomerID			   



/*	Result:		   

CustomerID Year        Total

---------- ----------- -----------

ANTON      1996        24

ANTON      1997        295

ANTON      1998        40

BONAP      1996        181

BONAP      1997        486

BONAP      1998        313

BOTTM      1996        81

BOTTM      1997        454

BOTTM      1998        421

*/

/*

2->pivot根據(jù)FOR [Year] IN子句中的值，在結(jié)果集中來建立對應(yīng)的新列，本例中即是列，，

   對于新列，，中的取值，取中間結(jié)果集中與之相對應(yīng)的值。

   如對于客戶ANTON，1996列中的值就選擇中間結(jié)果中對應(yīng)的Total值，同理列中為。

   并將中間結(jié)果pivot表命名為x。

   

3->最外層的SELECT語句從pivot表生成最終結(jié)果,此處因Orders表僅有列，故直接將結(jié)果用一個SELECT返回，有嵌套的SELECT參照下例。

  

--結(jié)果:  

CustomerID 1996        1997        1998

---------- ----------- ----------- -----------

ANTON      24          295         40

BONAP      181         486         313

BOTTM      81          454         421

*/         以下是為輸入表多于一列的例子,數(shù)據(jù)來源于SQL server 2005的AdventureWorks，其實現(xiàn)的原理同上。

SELECT *  

FROM(

	SELECT YEAR(DueDate) [Year]

		   ,CASE MONTH(DueDate)

			WHEN 1 THEN 'January' 

			WHEN 2 THEN 'February'

			WHEN 3 THEN 'March'

			WHEN 4 THEN 'April'

			WHEN 5 THEN 'May'

			WHEN 6 THEN 'June'

			WHEN 7 THEN 'July'

			WHEN 8 THEN 'August'

			WHEN 9 THEN 'September'

			WHEN 10 THEN 'October'

			WHEN 11 THEN 'November'

			WHEN 12 THEN 'December'

		   END as [Month]

		   ,ProductID

		   ,OrderQty

	FROM Production.WorkOrder

)WorkOrder

    PIVOT (

           SUM(OrderQty)

           FOR [Month] IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])

          )x

ORDER BY [Year], ProductID  

--Result: 末尾部分省略

/*  

Year        ProductID   January     February    March       April       May         June        July        August      

----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 

2002        3           8480        16870       12960       9530        19390       14170       26200       35870             

2002        316         1842        3704        2910        2252        4738        3496        7624        10778          

2002        324         1842        3704        2910        2252        4738        3496        7546        10600            

2002        327         921         1852        1455        1126        2369        1748        3773        5300              

2002        328         414         1048        872         458         1272        992         1786        2632         

*/

2. 動態(tài)PIVOT的使用

USE AdventureWorks;

GO 



--第一種生成透視列的方法，使用了COALESCE來聯(lián)接字符串

DECLARE @PivotColHeader VARCHAR(MAX)    

SELECT @PivotColHeader =

    COALESCE(@PivotColHeader + ',[' + cast(Name as varchar) + ']',

    '[' + cast(Name as varchar) + ']')   --示例中Name轉(zhuǎn)換為varchar或char類型，注意：在CAST 和CONVERT 中使用varchar 時，顯示n的默認值為30

FROM Sales.SalesTerritory

GROUP BY Name



/*

--第二種生成透視列的方法，使用了FOR XML PATH方法

SELECT @PivotColHeader = 

    STUFF(

    (     

          SELECT DISTINCT ',[' + cast(Name as varchar) + ']'

          FROM Sales.SalesTerritory

          FOR XML PATH('')

    ),

    1,1,'')

*/



DECLARE @PivotTableSQL NVARCHAR(MAX)

SET @PivotTableSQL = N'

    SELECT *

    FROM (

        SELECT YEAR(H.OrderDate) [Year]

			,T.Name

			,H.TotalDue

        FROM Sales.SalesOrderHeader H

			LEFT JOIN Sales.SalesTerritory T

			    ON H.TerritoryID = T.TerritoryID

    )AS PivotData

        PIVOT(

            SUM(TotalDue)

            FOR Name IN (

                         ' + @PivotColHeader + '

                         )

               ) AS x '                            

EXECUTE sp_executesql @PivotTableSQL    



--Result:部分結(jié)果省略

/*

Year        Australia             Canada                Central               France                Germany               Northeast             

----------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- 

2001        1446497.1744          2173647.1453          1263884.1024          199531.723            262752.4184           754833.2045           

2002        2380484.8387          7215430.5017          3518185.4756          1717145.7439          575960.0974           3275322.1694          

2003        4547123.2777          8186021.9178          4015356.874           4366078.3475          2714826.4297          3833030.25           

2004        3823410.2386          3926712.8926          1771532.7396          2853948.6596          2386224.5508          1406555.6861         



*/

?? 對該動態(tài)pivot增加匯總列

DECLARE @PivotColHeader VARCHAR(MAX)

DECLARE @TotalCol VARCHAR(MAX)



SELECT @PivotColHeader =                              --使用COALESCE函數(shù)生成列標題

    COALESCE(@PivotColHeader + ',[' + cast(Name as varchar) + ']',

    '[' + cast(Name as varchar) + ']')

    ,

    @TotalCol = COALESCE(@TotalCol + ', SUM([' + cast(Name as varchar) + ']) AS [' + cast(Name as varchar) + ']'

     ,'SUM([' + cast(Name as varchar) + ']) AS [' + cast(Name as varchar) + ']')     --使用COALESCE函數(shù)生成匯總字符串

FROM Sales.SalesTerritory



DECLARE @PivotTableSQL NVARCHAR(MAX)

SET @PivotTableSQL = N'

    SELECT *

    FROM (

        SELECT CAST(YEAR(H.OrderDate) AS CHAR(4)) [Year]

		,T.Name

		,H.TotalDue

        FROM Sales.SalesOrderHeader H

		LEFT JOIN Sales.SalesTerritory T

		   ON H.TerritoryID = T.TerritoryID

    )AS PivotData

		PIVOT(

			SUM(TotalDue)

			FOR Name IN (

			 ' + @PivotColHeader + '

				   )

		    ) AS x   

    UNION 

    SELECT ''GrandTotal'', ' + @TotalCol + '

    FROM (

	SELECT CAST(YEAR(H.OrderDate) AS CHAR(4)) [Year]

		,T.Name

		,H.TotalDue

	FROM Sales.SalesOrderHeader H

	    LEFT JOIN Sales.SalesTerritory T

		ON H.TerritoryID = T.TerritoryID

	) AS PivotData													

		PIVOT(

			SUM(TotalDue)

			FOR Name IN (

				' + @PivotColHeader + '

				   )

		     ) AS y '   

--PRINT  @PivotTableSQL                                        

EXECUTE sp_executesql @PivotTableSQL 



--Result:部分結(jié)果省略

/*

Year       Australia             Canada                Central               France                Germany               Northeast             Northwest             

---------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- 

2001       1446497.1744          2173647.1453          1263884.1024          199531.723            262752.4184           754833.2045           2703481.7947          

2002       2380484.8387          7215430.5017          3518185.4756          1717145.7439          575960.0974           3275322.1694          5651688.6685          

2003       4547123.2777          8186021.9178          4015356.874           4366078.3475          2714826.4297          3833030.25            7494658.0357          

2004       3823410.2386          3926712.8926          1771532.7396          2853948.6596          2386224.5508          1406555.6861          4952772.2793          

GrandTotal 12197515.5294         21501812.4574         10568959.1916         9136704.474           5939763.4963          9269741.31            20802600.7782         

*/

生成匯總列的注意事項;
??? 1->使用COALESCE函數(shù)生成列標題。
??? 2->使用COALESCE函數(shù)生成帶有SUM求和函數(shù)并且指定了別名的字符串。
??? 3->使用UNION對兩個SELECT來實現(xiàn)聯(lián)接。且將[Year]轉(zhuǎn)換為字符串，因為YEAR(H.OrderDate)得值為 INT ，而''GrandTotal''為字符串，UNION 或UNION ALL使用時必須列的數(shù)量和類型相對應(yīng)。

SQL server 2005 PIVOT運算符的使用

更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯(lián)系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

一、PIVOT的語法

一、PIVOT的語法