理解T-SQL中的枢轴函数

Question

理解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

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

Answer 1

这是一个非常基本的枢轴例子，请仔细看一遍。Sqlserver-枢轴和UNPIVOT表示例下面是产品表的上述链接中的示例：SELECT PRODUCT, FRED, KATEFROM (SELECT CUST, PRODUCT, QTYFROM Product) up PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvtORDER BY PRODUCT呈现： PRODUCT FRED  KATE --------------------  BEER     24    12  MILK      3     1  SODA   NULL     6  VEG    NULL     5类似的例子可以在博客文章中找到。SQL Server中的枢轴表。一个简单的样本

Answer 2

&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