Select into statement in a view

Question

I want make a view of a select statement that uses a temp table but I am getting errors about views may not have temp tables. How can I resolve this? This is my query

CREATE VIEW vwTopStackedItems
AS
SELECT COUNT(INVENTORY.itemID) as Stacks, (ITEM.itemID), CHARACTERS.charName      
INTO #Table1
FROM INVENTORY
INNER JOIN ITEM
ON INVENTORY.itemID = ITEM.itemID
JOIN CHARACTERS
ON INVENTORY.charID = CHARACTERS.charID
WHERE INVENTORY.quantity>1
GROUP BY CHARACTERS.charName, ITEM.itemID 


SELECT [ITEMID], 
   LEFT(column_names , LEN(column_names )-1) AS column_names, 
   SUM([Stacks]) total_stacks
FROM #Table1 AS extern
CROSS APPLY
(
SELECT [charName] + ','
   FROM #Table1 intern
    WHERE intern.[ITEMID] = extern.[ITEMID]
FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY [ITEMID], column_names;
GO

Answer 1

You cannot use temp tables when creating views. You could try using a common-table-expression instead. Not 100% sure using it with cross apply will work though. If not, another option would be to move the temp table to inline subqueries instead.

Here's with a CTE:

CREATE VIEW vwTopStackedItems
AS
WITH CTE AS (
    SELECT COUNT(INVENTORY.itemID) as Stacks, (ITEM.itemID), CHARACTERS.charName      
    FROM INVENTORY
    INNER JOIN ITEM
    ON INVENTORY.itemID = ITEM.itemID
    JOIN CHARACTERS
    ON INVENTORY.charID = CHARACTERS.charID
    WHERE INVENTORY.quantity>1
    GROUP BY CHARACTERS.charName, ITEM.itemID 
)
SELECT [ITEMID], 
   LEFT(column_names , LEN(column_names )-1) AS column_names, 
   SUM([Stacks]) total_stacks
FROM CTE AS extern
CROSS APPLY
(
   SELECT [charName] + ','
   FROM CTE intern
   WHERE intern.[ITEMID] = extern.[ITEMID]
   FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY [ITEMID], column_names;
GO