时间戳

首页 > 解决方案 > 尝试创建 PIVOT 表时，我在 SQL 中收到无效标识符错误

sql-server - 尝试创建 PIVOT 表时，我在 SQL 中收到无效标识符错误

问题描述

SELECT ProdName, Sales, 
    1 AS January, 2 AS February, 3 AS March, 4 AS April, 5 AS May, 6 AS June,
    7 AS July, 8 AS August, 9 AS September, 10 AS October,
    11 AS November, 12 AS December 
FROM
(SELECT actsales as Sales , EXTRACT (month from factdate) as Month, ProdName
FROM factcoffee, prodcoffee
WHERE factcoffee.ProductId = prodcoffee.productid) 
PIVOT  
(  
SUM (Sales)  
FOR Month IN  
( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 )  
);

我在第 1 行得到了 Sales 的错误。如果我只从第 1 行删除销售额，我会根据需要将产品的数据透视表作为行，将月份作为列，但值不是销售额，而只是 1一月专栏，二月专栏2，依此类推。如果我在第 1 行执行 SELECT *，我会按月获得每种产品的正确销售价值。但列名显然不是月份名称，只是 1,2，......

标签： sql-server

解决方案

您的查询很好，但请尝试使其更具可读性

SELECT ProdName
, Sales
,[1] AS January
,[2] AS February
,[3] AS March
,[4] AS April
,[5] AS May
,[6] AS June
,[7] AS July
,[8] AS August
,[9] AS September
,[10] AS October
,[11] AS November
,[12] AS December 
 FROM
         (SELECT actsales as Sales , EXTRACT (month from factdate) as Month, 
          ProdName
          FROM factcoffee, prodcoffee
          WHERE factcoffee.ProductId = prodcoffee.productid) 
PIVOT  
(  
SUM (Sales)  
FOR Month IN  
( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )  
) as PVT;

不要忘记将枢轴重命名为 PVT 或其他名称。有时代码会因为这样做或忘记括号而失败。

此外，如果您可以尝试使用 join 而不是FROM factcoffee, prodcoffee.它更具可读性

推荐阅读