0

I have created query for converting vertical table data into horizontal, and I have a declare statement in my query.

I have to create a view based on my query, but I get an error from SQL Server since I have a declare statement in my query.

The query that I am trying to turn into a view:

declare @columnName nvarchar(max)
declare @query nvarchar(max)

select 
    @columnName = COALESCE(@columnName + ', ', '') + QUOTENAME(element_name) 
from 
    (select distinct 
         element_name 
     from 
         elements e 
     join 
         form_elements fe on fe.element_id = e.id 
     join 
         form on fe.form_id = form.id and form.id = 1) as B

set @query = 'select *
              from 
                  (select fi.index_key as incidnet_id, e.element_name as col_name, fev.value as value
                   from form_element_values fev
                   join form_index fi on fev.form_index_id = fi.id
                   join form_elements fe on fev.form_element_id = fe.id
                   join elements e on fe.element_id = e.id
                   join form f on fi.form_id = f.id
                   where f.id = 1) as SourceData 
              PIVOT(max(value) for col_name in (' + @columnName + ')) as pivotTable'

exec(@query)

This query takes the data from vertical table and show it in horizontal columns

As my column name are dynamic, I have to create a local variable but now I am having difficulty in converting this query into a view.

Is there any other alternative for this query so I don't have to use local variable?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
charmy
  • 5
  • 2
  • This uses dynamic SQL and a batch of instructions. That cannot be converted into a view. What about a stored procedure? – Alejandro Mar 15 '18 at 20:50
  • I cannot have stored procedure as we want to use the view to filter and sort and act as a table way. I tried creating Function but that failed too as i have 'exec(@query) at the end of the statement – charmy Mar 15 '18 at 22:09

1 Answers1

0

This cannot be turned into a view. If you check the documentation for CREATE VIEW you'll see that it indicates that the body of the view is just a plain SELECT statement and nothing else. It doesn't accepts any kind of control flow, variables or any other body, just a lone select with a few restrictions.

A view in a database is just that, a SELECT stored in the database, from which queries can be made and expanded, but nothing more than that. In this case your query requires dynamic SQL, and that's beyond the capabilities of a view. As you noted in comments, neither is possible with a function, as those cannot have anything that can have side effects, and running a batch of dynamic SQL can do virtually anything, therefore they're forbideen there.

Your only option to keep this on the DB side is to use a stored procedure. Also noted in comments is that you can't do anything with the result set other than consume it (not at least in any easy way, look here for some details, and also at this question).

Another viable alternative is to do all this client-side and leave the database to just run the query. You would issue the first select to get the required columns and then use your client language to issue another query doing the PIVOT.

Alejandro
  • 7,290
  • 4
  • 34
  • 59