理解T-SQL中的樞軸函數(shù)我對(duì)SQL非常陌生。我有一張這樣的桌子：ID | TeamID | UserID | ElementID | PhaseID | Effort-----------------------------------------------------1 | 1 | 1 | 3 | 5 | 6.742 | 1 | 1 | 3 | 6 | 8.253 | 1 | 1 | 4 | 1 | 2.234 | 1 | 1 | 4 | 5 | 6.85 | 1 | 1 | 4 | 6 | 1.5我被告知要得到這樣的數(shù)據(jù)ElementID | PhaseID1 | PhaseID5 | PhaseID6-------------------------------------------- 3 | NULL | 6.74 | 8.25 4 | 2.23 | 6.8 | 1.5我知道我需要使用樞軸函數(shù)。但不明白。如果有人能在上述情況下解釋它，那將是很有幫助的。(如果有的話)。
3 回答
慕沐林林
TA貢獻(xiàn)2016條經(jīng)驗(yàn) 獲得超9個(gè)贊
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
-- 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(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Here is the result set.
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
MM們
TA貢獻(xiàn)1886條經(jīng)驗(yàn) 獲得超2個(gè)贊
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
添加回答
舉報(bào)
0/150
提交
取消