How to filter the result of an execute(@query) by

问题:

I'd like to know how to filter (put where clause) in the result of a dynamic query like this:

execute(@query)

The problem is the number of columns of @query is dynamic and titles are unknown because the query is the result of a PIVOT:

TSQL creating a dynamic report from two tables, one table is holds the headers, other one, data

The result I want to get is something like this:

select * from execute(@query) where column(1) = 'something'

Thanks for your help in advance.

回答1:

set @query = N'select * from (' + @query + N') t where [col1] = ''something''';

exec (@query)

You could use the unpivoted table to figure out what the name of the column would be, and use this in place of col1.