I have created a dynamic SQL query that I want to use as a view, however, the query is dependent on using 'DECLARE' statements. I have tried unsuccessfully to restructure it without the 'DECLARE' statements, but can't quite get it right. I am using SQL Server Express 2014 and would appreciate any and all help.
DECLARE @query nvarchar(MAX)
DECLARE @Name nvarchar(MAX)
select @Name = STUFF((SELECT distinct ',' + QUOTENAME(Name)
FROM [dbo].[ObjectView]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = ' SELECT * from
(
select *
from [dbo].[ObjectView]
)t
pivot (MAX(Value) for Name IN (' +@Name+ ')) AS PivotTable'
execute(@query)