Dynamic Column amount query

[Leo Muller]

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

Leo

[--CELKO--]

In 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.

[Erland Sommarskog]

Leo Muller (le***************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Tzvika Barenholz]

You may find this useful:

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