SEO

#1 (**permalink**) 02-29-2008, 06:51 AM

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

#2 (**permalink**) 02-29-2008, 06:51 AM

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.

#3 (**permalink**) 02-29-2008, 06:51 AM

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/techinf...2000/books.asp

#4 (**permalink**) 02-29-2008, 06:52 AM

You may find this useful:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Dynamic Column amount query