query returns only fields that have a value

[mbusa11]

How do I output fields that only have a valid value in the database and skip the output of fields that have No value in the database.

example

First Name : Jen
Middle Initial : A
Last Name : Brooke


if for example the person doesnt have a middle initial

I want the output to be like

First Name : Kelly
Last Name : Sue

Instead of

First Name : Kelly
Middle Initial :
Last Name : Sue

 

[gmmastros]

You could use dynamic SQL to do this, but I recommend against it.

Instead, you should modify your front end app so that it ignores fields that are blank (i.e. don't show it to the user).



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

This will only work if you return 1 row only
what if row 1 has a middle name and row 2 doesn't, what do you do in that case

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLBill]

Maybe:

WHERE FirstName IS NOT NULL
        OR MiddleName IS NOT NULL
        OR LastName IS NOT NULL

-SQLBill

Posting advice: FAQ481-4875

 

[mbusa11]

thanx for the response guys .. but i have no choice, but to put it in the query , so it cannot be on the front end..

as far as bills post.. id like to hear more.. what exactly do u mean by puttint that stuff in there where clause.. sorry im a newbie to this..

can u like give me a simple query as an example..

 

[vongrunt]

If first/middle/last name are columns then... tables are simply said squared. You cannot drop/hide a column here and there. Either entire column is present or not.

What is exact table structure you have?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)