I made a query that pivots a table using names from another table. I asked the IT folks to make it into a view because there's a need to be able to query the pivoted result. The query, which works fine, is below. The response was that it couldn't be made into a view because it's actually 2 queries. Is this true? Is there a way it could be redone so that it could be used to make a view?
DECLARE @NPDColumns VARCHAR (8000)
SELECT @NPDColumns =
COALESCE (@NPDColumns + ',[' + cast (Description AS VARCHAR) + ']',
'[' + cast (Description AS VARCHAR) + ']')
GROUP BY Description
DECLARE @query VARCHAR (8000)
SET @query = '
DECLARE @NPD TABLE
( BatchCode NVARCHAR (20),
Description NVARCHAR (20),
INSERT INTO @NPD (BatchCode, Description, Actual)
SELECT a.BatchCode, b.Description, a.Actual
FROM [RKBBackEnd].[dbo].[tblNPDBatch] AS a
[RKBBackEnd].[dbo].[lkpNPDBatch] AS b
ON a.BatchAttributeID = b.BatchAttributeID
--WHERE BatchCode LIKE '+ CHAR(39) + '201203%' + CHAR(39)+ '
SELECT c.BatchAcceptDateTime, d.*
FROM (SELECT BatchCode, BatchAcceptDateTime
FROM [RKBBackEnd].[dbo].[tblBatchInfo]) AS c
FROM @NPD PIVOT (SUM (Actual)
+ ')) AS P) AS d
ON c.BatchCode = d.BatchCode
ORDER BY BatchCode'