CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pr0no
Pr0no

Reputation: 4099

How can I re-use the output of one query in another query?

Please consider the following snippet. I use stuff() to generate a list of fields. I use this same list in the following query.

select top 1
  stuff((
        select ',' + 'D.[' + ColumnName + ']'
              from #report_fields a
              where a.FieldType like 'X' or a.FieldType like 'Y'
              for XML path('')),1,1,'') as document_fields
  from #report_fields a
;


-- returns a fieldlist: D.[AAA], D.[BBB], D.[CCC], ...

select
    D.[AAA], D.[BBB], D.[CCC], ...
into
    #document_fields
from
    Document D
group by
    D.[AAA], D.[BBB], D.[CCC], ...

My problem: I need the fieldlist in the second query to be dynamic. I want to inject the output of stuff() directly into the select...from document query. What is the most straight-forward way of doing this? Can I put the fieldlist in a variable (never done this before in SQL) or do I need a dynamic query (which I am trying to avoid for many reasons).

You help is greatly appreciated.

[edit]

declare @fieldlist nvarchar(max)
select top 1 @fieldlist = 
  stuff((
        select ',' + 'D.[' + ColumnName + ']'
              from #report_fields a
              where a.FieldType like 'Text' or a.FieldType like 'Boolean'
              for XML path('')),1,1,'')
  from #report_fields a

declare @sql nvarchar(max)
select @sql = 'select ' + @fieldlist + ' into #document_fields from Document D     group by ' + @fieldlist
exec sp_execute @sql

Upvotes: 0

Views: 90

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6771

I believe this has to be done via dynamic sql:

DECLARE @Fieldlist NVARCHAR(MAX)

select top 1 @Fieldlist = 
  stuff((
        select ',' + 'D.[' + ColumnName + ']'
              from #report_fields a
              where a.FieldType like 'X' or a.FieldType like 'Y'
              for XML path('')),1,1,'')
  from #report_fields a

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = 'select '
    + @Fieldlist + '
into
    #document_fields
from
    Document D
group by '
   + @Fieldlist

   EXEC sp_executesql @SQL

Upvotes: 1

Related Questions