Cross Tabulation Msg 8120, Level 16, State 1, Line 8

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 E

Solution 1:

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:

GROUPBYCONVERT(char(7),Production.Production.[Date] , 112), Production.Production.ItemID

So your full query will be:

SELECT t,     
    MAX(CASE ItemID WHEN1THEN qun  ELSE''END) AS [Item-A],    
    MAX(CASE ItemID WHEN2THEN qun  ELSE''END) AS [Item-B],    
    MAX(CASE ItemID WHEN3THEN qun  ELSE''END) AS [Item-C],
    MAX(CASE ItemID WHEN4THEN qun  ELSE''END) AS [Item-D],
    MAX(CASE ItemID WHEN5THEN qun  ELSE''END) AS [item-E]
FROM 
(
    SELECTCONVERT(char(7),Production.Production.[Date] , 112) as t, 
        sum(Production.Production.Quantity) qun, 
        Production.Production.ItemID ItemID
    FROM    Production.Production 
    GROUPBYCONVERT(char(7),Production.Production.[Date] , 112), Production.Production.ItemID
)AS e 
GROUPBY e.t