Dynamic columns for Pivot

Question

I have a table of equipment, a table of customers and a sparse table of equipment and customers. I want to produce a query that returns a 1 if the equipment/Customer table record exists and a 0 if it doesn't for each customer. There are only a small number of customers in this problem, so what I want back should look like this:

EquipmentID  Cust1  Cust2 Cust3 1234101 1357010 2234100

I can use a Cross Join to get a master list of possible records, but that returns the information in rows. I want to see it in columns, but the PIVOT function requires that I name the columns first. What I need is for the column names (Cust1, Cust2...) to be dynamic.

Answer 1

If you want to do that, then you have to create the query string dynamically and then pass it to the sp_execute stored procedure. AFAIK, there isn't a way to do it without constructing the query string dynamically.

Answer 2

If you have a reporting tool handy use your current query and make a "martix", "cross-tab", or "pivot table" report.