CREATE TABLE dbo.AverageFishLength
(
Fishtype VARCHAR(50),AvgLength DECIMAL(8,2),FishAge_Years INT
)
INSERT INTO dbo.AverageFishLength
( Fishtype,AvgLength,FishAge_Years )
VALUES ( 'Muskie',32.75,3 ),( 'Muskie',37.5,4 ),39.75,5 ),( 'Walleye',16.5,18.25,20.0,( 'Northern Pike',20.75,23.25,26.0,5 );
这是数据透视查询：
SELECT Fishtype,[3] AS [3 Years Old],[4] AS [4 Years Old],[5] AS [5 Years Old]
FROM dbo.AverageFishLength PIVOT( SUM(AvgLength)
FOR FishAge_Years IN ( [3],[4],[5] ) ) AS PivotTbl
结果如下：
但是,如果我使用标识列创建表,结果将分成不同的行：
DROP TABLE dbo.AverageFishLength
CREATE TABLE dbo.AverageFishLength
(
ID INT IDENTITY(1,1),Fishtype VARCHAR(50),5 );
同样的查询：
SELECT Fishtype,[5] ) ) AS PivotTbl
结果不同：
在我看来,ID列正在查询中使用,即使它根本没有出现在查询中.它几乎就像隐含在查询中,但未在结果集中显示.
谁能解释为什么会这样？
解决方法
MSDN docs about FROM的文档说明如下：
table_source PIVOT <pivot_clause>Specifies that the table_source is pivoted based on the pivot_column. table_source is a table or table expression. The output is a table that contains all columns of the table_source except the pivot_column and value_column. The columns of the table_source,except the pivot_column and value_column,are called the grouping columns of the pivot operator.
PIVOTperforms a grouping operation on the input table with regard to the grouping columns and returns one row for each group. Additionally,the output contains one column for each value specified in the column_list that appears in the pivot_column of the input_table.
你的版本基本上是说SELECT * FROM yourtable和PIVOT那些数据.即使ID列不在最终的SELECT列表中,它也是查询的分组元素.如果您将PIVOT与“pre-PIVOT”示例进行比较,以显示您将看到您的版本.此示例使用CASE表达式和聚合函数：
SELECT Fishtype,sum(case when FishAge_Years = 3 then AvgLength else 0 end) as [3],sum(case when FishAge_Years = 4 then AvgLength else 0 end) as [4],sum(case when FishAge_Years = 5 then AvgLength else 0 end) as [5] FROM dbo.AverageFishLength GROUP BY Fishtype,ID;
结果将会有所偏差,因为即使您在最终列表中没有ID,它仍然被用于分组,因为它们是唯一的,您将获得多行.
SELECT Fishtype,[5] AS [5 Years Old] FROM ( SELECT Fishtype,FishAge_Years FROM dbo.AverageFishLength ) d PIVOT ( SUM(AvgLength) FOR FishAge_Years IN ( [3],[5] ) ) AS PivotTbl;
在此版本中,您只返回表中实际需要和想要的列 – 这会排除ID,因此不会用于对数据进行分组.
