Dynamic Column amount query

I want to write a query that will give me a dynamic amount of columns back.
What I want to do, I want to create a calendar application, in which for
each employee, I want to show if he is in the office or not.

this should look like:
ID, Name, 1,2,3,4,5,6,7,8
88,Leo,0,0,1,1,1,0,0,1

The amount of columns is dynamic, and is a period of time, with a column for
each day.
Any suggestions what the best approach to this could be?

Thanks

LeoIn SQL, a table has a known number of columns. What you are trying to
do is destroy First Normal Form (1NF). In a tiered architecture, such
fomatting is done in the front end and not the database.|||Leo Muller (leo_muller_israel@.hotmaill.com) writes:
> I want to write a query that will give me a dynamic amount of columns
> back. What I want to do, I want to create a calendar application, in
> which for each employee, I want to show if he is in the office or not.
> this should look like:
> ID, Name, 1,2,3,4,5,6,7,8
> 88,Leo,0,0,1,1,1,0,0,1
> The amount of columns is dynamic, and is a period of time, with a column
> for each day.

As Joe Celko says, relational databases are not meant for this kind of
thing. A query returns a table, and a table has a fixed number of columns.

This sort of thing may be best do client-side.

Nevertheless this is possible in SQL Server, but you will have to resort
to dynamic SQL, and it's all quite complicated.

An alternative is to use the third-party tool, RAC, see
http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You may find this useful:

http://www.windowsitpro.com/Article...3140/43140.html