SQL Query for filtering columns returned

Question

I want to return columns based on some meta data in an other table. i.e. i have my table which contains 10 columns, and another table which contains those columns denormalise with metadata to do with them.

i.e. Table - Car:

columns - Make,Model,Colour

and another table called "Flags" which has a row for each of the above columns and each row has a column for "IsSearchable" and "ShowOnGrid" - that sort of thing. The query i want is one which will return all columns from the cars table that are flagged in the "Flags" table as "ShowInGrid"

----EDIT

Apologise, I should have stated that this is on SQL Server 2008. Also, I dont want to have to physically state the columns which i would like to return, i.e. If i add a column to the car table, then add it into the Flags table and declare it to be searchable, I don't want to have to physically state in the SQL Query that i want to return that column, i want it to automatically pull through.

Answer 1

You need to use dynamic SQL; this can easily be done with a stored procedure.

Answer 2

Something like this might work:

Select 
  D.CarID,
  Case D.ShowMake When True Then D.Make Else NULL END AS Make
  ...
From
  (Select
    C.CarID, C.Make, C.Model, C.Colour, F.IsSearchable, F.ShowOnGrid, F.ShowMake
  From 
    Cars C
  Inner Join
    Flags F
      On C.CarID = F.CarID) D

I didn't write in all the case statements and don't know how many flags you're working, but you can give it a try. It would require to filter on null values in your application. If you actually want the columns omitted on the basis of the Flag column value the other answer and comment are both right on. Either Dynamic SQL or build your query outside in another language first.