Data Source Column Changes

I have some Dynamic SQL that builds a table with the top 20 item numbers as columns, Stores as rows and an X marking the intersection of Store/Item. This produces a table which is a matrix showing which stores are low on which items.
This table is generated once a week and each week will have different column names because the item numbers which the stores are low on changes from week to week.
How can I write a report that will handle the column names of the data table changing without having to do maintenance on the report every week?

Thanks,
Mark Redman.

One way of doing this is to move the dynamic SQL into a stored procedure and return a dataset which always has fixed column names. The actual item information (item number) would be e.g. the first row in that generated dataset.

-- Robert

Data from Derived Table

I am looking to run two different queries.

the first one returns about 6000 numbers. i.e 123454, 15432, 2343545 etc

My second query i want to use the results from the first query as a where clause?

i.e

where Number in ('123454','15432','2343545')

Clearly i dont want to type out 6000 numbers in a query. How do i use the data generated in the first query as the where criterea in the second query.

You can use the following query...(subquery)

Select Numbers From SomeTable -- Which returns 6000 numbers

on your second query..

..

..

Where Number in(Select Numbers From Sometable)