交叉列表Msg 8120，级别16，状态1，第8行 [英] cross tabulation Msg 8120, Level 16, State 1, Line 8

问题描述

我正在尝试在sql server express 2012中执行以下操作

I'm trying to execute the following in sql server express 2012

SELECT t,     
  MAX(CASE ItemID WHEN 1 THEN qun  ELSE  '' END) AS [Item-A],    
  MAX(CASE ItemID WHEN 2 THEN qun  ELSE  '' END) AS [Item-B],    
  MAX(CASE ItemID WHEN 3 THEN qun  ELSE  '' END) AS [Item-C],
  MAX(CASE ItemID WHEN 4 THEN qun  ELSE  '' END) AS [Item-D],
  MAX(CASE ItemID WHEN 5 THEN qun  ELSE  '' END) AS [item-E]
FROM 
(
   SELECT  CONVERT(char(7),Production.Production.[Date] , 112)as t, 
        sum(Production.Production.Quantity) qun, 
        Production.Production.ItemID ItemID
    FROM    Production.Production 
)AS e 
GROUP BY e.t

但是我得到了错误:

消息8120，第16级，状态1，第8行

Msg 8120, Level 16, State 1, Line 8

选择列表中的'Production.Production.Date'列无效，因为它既不包含在聚合函数中也不在GROUP BY子句中.

Column 'Production.Production.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

推荐答案

由于您在子查询中使用了聚合函数，因此您需要对选择列表中未聚合的列使用GROUP BY函数.您需要添加以下行:

Since you are using an aggregate function in your subquery you need to use a GROUP BY function for the columns in your select list that are not being aggregated. You need to add the line:

GROUP BY CONVERT(char(7),Production.Production.[Date] , 112), Production.Production.ItemID

因此，您的完整查询将是:

So your full query will be:

SELECT t,     
    MAX(CASE ItemID WHEN 1 THEN qun  ELSE  '' END) AS [Item-A],    
    MAX(CASE ItemID WHEN 2 THEN qun  ELSE  '' END) AS [Item-B],    
    MAX(CASE ItemID WHEN 3 THEN qun  ELSE  '' END) AS [Item-C],
    MAX(CASE ItemID WHEN 4 THEN qun  ELSE  '' END) AS [Item-D],
    MAX(CASE ItemID WHEN 5 THEN qun  ELSE  '' END) AS [item-E]
FROM 
(
    SELECT  
        CONVERT(char(7),Production.Production.[Date] , 112) as t, 
        sum(Production.Production.Quantity) qun, 
        Production.Production.ItemID ItemID
    FROM    Production.Production 
    GROUP BY CONVERT(char(7),Production.Production.[Date] , 112), Production.Production.ItemID
)AS e 
GROUP BY e.t

这篇关于交叉列表Msg 8120，级别16，状态1，第8行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！