470,636 Members | 1,655 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,636 developers. It's quick & easy.

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

Leo
Jul 23 '05 #1
3 1431
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.

Jul 23 '05 #2
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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Tricon | last post: by
1 post views Thread by Nathan Bloomfield | last post: by
3 posts views Thread by Peter Bailey | last post: by
2 posts views Thread by deejayquai | last post: by
???
1 post views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.