query to return only non null fields

Is it possible to write a query that returns only non null fields from a specified record? I have a big table with a record for each customer. the record contains a field for each item that can be purchased (only 6 items). I need to write an invoice but not every customer buys every product. I get the feeling Im going about this all wrong. Any help would be great

ThanksOriginally posted by nicky w
Is it possible to write a query that returns only non null fields from a specified record? I have a big table with a record for each customer. the record contains a field for each item that can be purchased (only 6 items). I need to write an invoice but not every customer buys every product. I get the feeling Im going about this all wrong. Any help would be great

Thanks
It would have been better to have the up to 6 items as up to 6 records in a separate table. No SQL query can return a variable number of columns, you would have to write some procedural code to run the query and then present the NOT NULL data.|||are you still in a designing stage? then you should change the design.
What if more items will be offered?

referential integrity will prevent "lost childs"

otherwise andrew is right. write some procedural code

Query Time Limit?

I know I can use SET QUERY_GOVERNOR_COST_LIMIT to prevent a query from executing if its estimated execution time exceeds the value of the specified time in seconds, but is there some way to have a query stop executing (and raise an error) if the time exceeds a specified time? For example, if the query takes longer than 20 seconds, have it stop executing and raise an error?

Thanks,

-Dave

It prevents query from executing based on the cost rather than controlling resources.

BOL

'If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run indefinitely.

"Query cost" refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

|||

If you are referring to having the ability to control any one specific query -that is not possible.

QUERY_GOVERNOR_COST_LIMIT effects ALL queries executed on the server. It prevents a query from executing IF the estimated time exceeds the limit. Say the QUERY_GOVERNOR_COST_LIMIT was 120 seconds, the estimated plan was 119 seconds -the query would then execute EVEN if the resulting time required exceeded 120 seconds. As far as I am aware, there is no way to have an executing query abort at some predetemined elapsed time value.

Of course, from an application, it might be possible to spawn a thread that executed a query against SQL Server, and then the parent thread could abort the executing thread after a predetermined time. But that could be messy...