vendredi 8 juillet 2016

Select into statement in a view

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

Aucun commentaire:

Enregistrer un commentaire