理解T-SQL中的枢轴函数

理解T-SQL中的枢轴函数

我对SQL非常陌生。


我有一张这样的桌子:


ID | TeamID | UserID | ElementID | PhaseID | Effort

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

1  |   1    |  1      |   3       |  5     |   6.74

2  |   1    |  1      |   3       |  6     |   8.25

3  |   1    |  1      |   4       |  1     |   2.23

4  |   1    |  1      |   4       |  5     |   6.8

5  |   1    |  1      |   4       |  6     |   1.5

我被告知要得到这样的数据


ElementID | PhaseID1 | PhaseID5 | PhaseID6

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

    3     |   NULL   |   6.74   |   8.25

    4     |   2.23   |   6.8    |   1.5

我知道我需要使用枢轴函数。但不明白。如果有人能在上述情况下解释它,那将是很有帮助的。(如果有的话)。


慕少森
浏览 380回答 3
3回答

慕沐林林

&nbsp; &nbsp;SELECT <non-pivoted column>,&nbsp; &nbsp; [first pivoted column] AS <column name>,&nbsp; &nbsp; [second pivoted column] AS <column name>,&nbsp; &nbsp; ...&nbsp; &nbsp; [last pivoted column] AS <column name>FROM&nbsp; &nbsp; (<SELECT query that produces the data>)&nbsp; &nbsp; AS <alias for the source query>PIVOT(&nbsp; &nbsp; <aggregation function>(<column being aggregated>)FOR[<column that contains the values that will become column headers>]&nbsp; &nbsp; IN ( [first pivoted column], [second pivoted column],&nbsp; &nbsp; ... [last pivoted column])) AS <alias for the pivot table><optional ORDER BY clause>;USE AdventureWorks2008R2 ;GOSELECT DaysToManufacture, AVG(StandardCost) AS AverageCost&nbsp;FROM Production.ProductGROUP BY DaysToManufacture;&nbsp; &nbsp; DaysToManufacture&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AverageCost0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5.08851&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 223.882&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 359.10824&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 949.4105&nbsp; &nbsp; -- Pivot table with one row and five columnsSELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,&nbsp;[0], [1], [2], [3], [4]FROM(SELECT DaysToManufacture, StandardCost&nbsp;&nbsp; &nbsp; FROM Production.Product) AS SourceTablePIVOT(AVG(StandardCost)FOR DaysToManufacture IN ([0], [1], [2], [3], [4])) AS PivotTable;Here is the result set.Cost_Sorted_By_Production_Days&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp;AverageCost&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5.0885&nbsp; &nbsp; 223.88&nbsp; &nbsp; 359.1082&nbsp; &nbsp; NULL&nbsp; &nbsp; 949.4105

MM们

这是一个非常基本的枢轴例子,请仔细看一遍。Sqlserver-枢轴和UNPIVOT表示例下面是产品表的上述链接中的示例:SELECT&nbsp;PRODUCT,&nbsp;FRED,&nbsp;KATEFROM&nbsp;(SELECT&nbsp;CUST,&nbsp;PRODUCT,&nbsp;QTYFROM&nbsp;Product)&nbsp;up&nbsp;PIVOT&nbsp;(SUM(QTY)&nbsp;FOR&nbsp;CUST&nbsp;IN&nbsp;(FRED,&nbsp;KATE))&nbsp;AS&nbsp;pvtORDER&nbsp;BY&nbsp;PRODUCT呈现:&nbsp;PRODUCT&nbsp;FRED&nbsp;&nbsp;KATE&nbsp;-------------------- &nbsp;BEER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;24&nbsp;&nbsp;&nbsp;&nbsp;12 &nbsp;MILK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &nbsp;SODA&nbsp;&nbsp;&nbsp;NULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6 &nbsp;VEG&nbsp;&nbsp;&nbsp;&nbsp;NULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5类似的例子可以在博客文章中找到。SQL Server中的枢轴表。一个简单的样本
打开App,查看更多内容
随时随地看视频慕课网APP

相关分类

MySQL