Hiding NULL columns from result set

Hello colleagues, I have the following table, that has two flags - show
quantity, price or both. If a flag is not set I would like the corresponding
column to not be included in the result set.
CREATE TABLE Table1 (
Id int IDENTITY (1, 1) NOT NULL ,
Name nvarchar(20) NOT NULL,
Qty int NOT NULL,
Price int NOT NULL,
ShowQty int NOT NULL,
ShowPrice int NOT NULL
)
go
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
106, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second', 22,
120, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
134, 0, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth', 44,
90, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
72, 0, 0)
SELECT
SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
null - so it should be hidden*/
FROM
Table1
CheersYOu should handle that from your client application, you cant hide an
expression if you name it in the Select statement. You can check the value
in your client application and reformat the resultset as needed.
May the forces be with you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"keber" <k@.c.com> schrieb im Newsbeitrag
news:ub9REUiZFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Hello colleagues, I have the following table, that has two flags - show
> quantity, price or both. If a flag is not set I would like the
> corresponding column to not be included in the result set.
> CREATE TABLE Table1 (
> Id int IDENTITY (1, 1) NOT NULL ,
> Name nvarchar(20) NOT NULL,
> Qty int NOT NULL,
> Price int NOT NULL,
> ShowQty int NOT NULL,
> ShowPrice int NOT NULL
> )
> go
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
> 106, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second',
> 22, 120, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
> 134, 0, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth',
> 44, 90, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
> 72, 0, 0)
>
> SELECT
> SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
> SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
> null - so it should be hidden*/
> FROM
> Table1
> Cheers
>|||You can accomplish a "variable result set" using a stored procedure as follo
ws:
create procedure usp_return_values
as
if (SELECT SUM(CASE WHEN ShowPrice=1 THEN Price END) FROM Table1) is null
SELECT SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty' FROM Table1
else
SELECT
SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price'
FROM
Table1
go
exec usp_return_values
Edgardo Valdez
MCSD, MCDBA, MCSE
"keber" wrote:

> Hello colleagues, I have the following table, that has two flags - show
> quantity, price or both. If a flag is not set I would like the correspondi
ng
> column to not be included in the result set.
> CREATE TABLE Table1 (
> Id int IDENTITY (1, 1) NOT NULL ,
> Name nvarchar(20) NOT NULL,
> Qty int NOT NULL,
> Price int NOT NULL,
> ShowQty int NOT NULL,
> ShowPrice int NOT NULL
> )
> go
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
> 106, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second', 22
,
> 120, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
> 134, 0, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth', 44
,
> 90, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
> 72, 0, 0)
>
> SELECT
> SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
> SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
> null - so it should be hidden*/
> FROM
> Table1
> Cheers
>
>|||In a tiered arachitecture, display is done in the front end and not the
database. This is a fundamental programming principle .. far more
fundamental than SQL.
We do not use assembly language styles flags in good SQL. Nor do we
use an IDENTITY columns when we have a relational key. Also why is a
price INTEGER and not DECIMAL()? Why do you think that a column is a
field, when they are totally different?
Look at what you wrote; each row would use zero, one or both of the
values in the summations. The results would be meaningless because you
have no data integrity.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117587045.951057.122160@.g14g2000cwa.googlegroups.com...
[ snip ]

> The results would be meaningless because you
> have no data integrity.
Ahhh ... but for some companies I've done work
for in the past, those are just the type of results
they're looking for. Meaningless.|||Celko, my young friend, I advice you to lose your attitude and not turn this
into a psychopathic discussion of how and what should look like in your
opinion. This is a simplified sample query used for dynamic reporting with
or without any front-end and several entry points. The flags are not a
business rule, they meant to directly affect the resultset.
cheers
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117587045.951057.122160@.g14g2000cwa.googlegroups.com...
> In a tiered arachitecture, display is done in the front end and not the
> database. This is a fundamental programming principle .. far more
> fundamental than SQL.
> We do not use assembly language styles flags in good SQL. Nor do we
> use an IDENTITY columns when we have a relational key. Also why is a
> price INTEGER and not DECIMAL()? Why do you think that a column is a
> field, when they are totally different?
> Look at what you wrote; each row would use zero, one or both of the
> values in the summations. The results would be meaningless because you
> have no data integrity.
>