sql question .. how can i dynamically add columns to a sql query result set?

Question

I am writing a report to return details about an object ('files') in my database. My application lets users create their own flags for use against file objects. Flags basically consist of a name, then flag instances store a bit value to indicate whether it is set for the parent file object.

I want to write a query that returns one row per file in the database, where the first few columns in the result set contain the file details (id, name, size etc) and the remaining columns are the flag names, with bit values returned to indicate whether the flag is set for the given file row.

Does that make sense? How do i go about writing the query?

Thanks for any help.

Edit: Clarification..

As part of this query, I need to run a sub query that returns the flags that have been created by the user (I do not know these at design time), then incorporate a check for each flag value in the main query to return details about the files.

Simplified schema as follows:

• File {Id, Name}
• Flag {Id, Name}
• FileFlags {FileId, FlagId} - a row in this table indicates that the flag is set for the file

I need the query to return a result set with columns something like this:

FileId FileName Flag1Name Flag2Name .... FlagNName

Answer 1

I think you want is an alias. Here is an example from http://www.sql-tutorial.net/SQL-Aliases.asp

SELECT Employee, SUM(Hours) AS SumHoursPerEmployee
FROM EmployeeHours
GROUP BY Employee

I would really have to see your schema to help further.

Edit Maybe you need nested SELECTs. http://sqlzoo.net/1a.htm

Answer 2

Sounds like perhaps you need a crosstab query, where you want to turn rows into columns. Are the flags stored in a separate table as a one to many relationship to the parent table?

Answer 3

You could start by looking at the Pivot function available in SQL Server 2005+. With that and some string concatenation you should be able to assemble a query for any number of columns

Based on your comment your select would look something like this:

        SELECT <non-pivoted column>,

            [first pivoted column] AS <column name>,

            [second pivoted column] AS <column name>, ...
    From Table 
    PIVOT (
...
FOR 
...
)

Answer 4

Why not create a stored procedure with the required logic - querying user-specific flags, etc - and on that basis dynamically build a query string with the variables you need?

You can then use EXECUTE to return the dynamic query set to the user. See http://msdn.microsoft.com/en-us/library/ms188332.aspx