0

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)
EAP
  • 1

1 Answers1

-3

You may have to play with the XML syntax. I'm pretty rusty.

create view viewname as
select * --< you really should call out the fields, here...
from(
    select * --< ...and here.
    from   ObjectView
  t
pivot( MAX( Value ) for Name in(
    select distinct QUOTENAME( Name ) 
    FROM ObjectView
    FOR XML PATH )
) AS PivotTable
TommCatt
  • 5,498
  • 1
  • 13
  • 20
  • PIVOT requires that the values inside the `IN` clause be know when it executes. SELECT statements cannot be used the way you've written this. – Taryn Jun 26 '14 at 19:37
  • Sorry to say this but you seem pretty rusty with the PIVOT syntax as well. – Andriy M Jun 26 '14 at 19:37
  • Well, this will be about the third time in my entire career of years that I've ever used PIVOT. Besides, that's the way it was in the question. That *is* a good excuse, isn't it? – TommCatt Jun 26 '14 at 21:53
  • 3
    I'd suggest making sure you understand the syntax before answering. The XML syntax is used in the question is to create the list of columns needed for the dynamic SQL string, you cannot use it in the PIVOT `IN`. I'd also make sure your syntax is valid before posting an answer. – Taryn Jun 26 '14 at 22:32