Hi,
can any one help me in solving this issue

i have a table test with 3 columns (city,state,country) of datatype
varchar and notnull


a b c
x y z
d e NULL
NULL b c

i need to select the values from the table which are not null.
on selecting from the table i need to get the output as

a b c
x y z
d e
b c

i should not return me the null column


Thanks in Advance

I am not sure I understand what is needed, since you start with
statement that the columns are not NULL and then provide sample data
with NULL values. Also, what do you mean not to return a column if it is
NULL? You can use COALESCE to change NULL values to something else, in
the example below to empty string:

SELECT COALESCE(city, '') AS city,
COALESCE(state, '') AS state,
COALESCE(country, '') AS country
FROM Test;

--
Plamen Ratchev
http://www.SQLStudio.com

On Sep 15, 12:33*pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> I am not sure I understand what is needed, since you start with
> statement that the columns are not NULL and then provide sample data
> with NULL values. Also, what do you mean not to return a column if it is
> NULL? You can use COALESCE to change NULL values to something else, in
> the example below to empty string:
>
> SELECT COALESCE(city, '') AS city,
> * * * * COALESCE(state, '') AS state,
> * * * * COALESCE(country, '') AS country
> FROM Test;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Hi,
Thanks for the reply.
when we use this query
SELECT COALESCE(city, '') AS city,
COALESCE(state, '') AS state,
COALESCE(country, '') AS country
FROM Test;

we get the result as

a b c
x y z
d e
b c

in 3rd row the 3rd column is empty and 4th row 1st column is empty .

my requirement is for example say i will select only one row

select * from Test where city="d";

it returns me a row like this
d e NULL

if i use COALESCE it will return me

d e (emptycolumn)

i need to return only two columns which has values ,i dont want the
column which is null to be returned.

city state
d e

this should be my result not

city state country
d e null


i think this requirement is clear.

Regards
Raj

raj wrote:

> On Sep 15, 12:33 pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
>> I am not sure I understand what is needed, since you start with
>> statement that the columns are not NULL and then provide sample data
>> with NULL values. Also, what do you mean not to return a column if it is
>> NULL? You can use COALESCE to change NULL values to something else, in
>> the example below to empty string:
>>
>> SELECT COALESCE(city, '') AS city,
>> COALESCE(state, '') AS state,
>> COALESCE(country, '') AS country
>> FROM Test;
>>
>> --
>> Plamen Ratchevhttp://www.SQLStudio.com
>
> Hi,
> Thanks for the reply.
> when we use this query
> SELECT COALESCE(city, '') AS city,
> COALESCE(state, '') AS state,
> COALESCE(country, '') AS country
> FROM Test;
>
> we get the result as
>
> a b c
> x y z
> d e
> b c
>
> in 3rd row the 3rd column is empty and 4th row 1st column is empty .
>
> my requirement is for example say i will select only one row
>
> select * from Test where city="d";
>
> it returns me a row like this
> d e NULL
>
> if i use COALESCE it will return me
>
> d e (emptycolumn)
>
> i need to return only two columns which has values ,i dont want the
> column which is null to be returned.
>
> city state
> d e
>
> this should be my result not
>
> city state country
> d e null
>
>
> i think this requirement is clear.

Not when considering multiple rows, as in your original example (where
one row had null country, another had null city).

SQL returns data in regular patterns. You should accept returned data
containing nulls, and interpret them as "no data", because that's what
nulls are for.

On Sep 15, 3:01*pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> raj wrote:
> > On Sep 15, 12:33 pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> >> I am not sure I understand what is needed, since you start with
> >> statement that the columns are not NULL and then provide sample data
> >> with NULL values. Also, what do you mean not to return a column if it is
> >> NULL? You can use COALESCE to change NULL values to something else, in
> >> the example below to empty string:
>
> >> SELECT COALESCE(city, '') AS city,
> >> * * * * COALESCE(state, '') AS state,
> >> * * * * COALESCE(country, '') AS country
> >> FROM Test;
>
> >> --
> >> Plamen Ratchevhttp://www.SQLStudio.com
>
> > Hi,
> > Thanks for the reply.
> > when we use this query
> > *SELECT COALESCE(city, '') AS city,
> > * * * * *COALESCE(state, '') AS state,
> > * * * * *COALESCE(country, '') AS country
> > *FROM Test;
>
> > we get the result as
>
> > a * * * * *b * * * * * * * c
> > x * * * * *y * * * * * * * z
> > d * * * * *e
> > * *b * * * * * * * c
>
> > in 3rd row the 3rd column is empty and 4th row 1st column is empty .
>
> > my requirement is for example say i will select only one row
>
> > select * from Test where city="d";
>
> > it returns me a row like this
> > d * * * * *e * * * * * * * NULL
>
> > if i use COALESCE it will return me
>
> > d * * * * *e * * * * * * * (emptycolumn)
>
> > i need to return only two columns which has values ,i dont want the
> > column which is null to be returned.
>
> > city state
> > d * * *e
>
> > this should be my result not
>
> > city state country
> > d * * e * * * null
>
> > i think this requirement is clear.
>
> Not when considering multiple rows, as in your original example (where
> one row had null country, another had null city).
>
> SQL returns data in regular patterns. *You should accept returned data
> containing nulls, and interpret them as "no data", because that's what
> nulls are for.- Hide quoted text -
>
> - Show quoted text -

Thanks alot..:-)

>> I should not return me the NULL column <<

A table is made of rows; all rows have the same structure. Each row
has a fixed number of columns of a fixed data type and with known
constraints. The rows ALWAYS have the same structure. A row is not a
record; a record can be variant, which is what you want.

I think that you need to read a book on RDBMS so you will not post
silly things on a newsgroup.

raj (2rajesh.b@gmail.com) writes:
> my requirement is for example say i will select only one row
>
> select * from Test where city="d";
>
> it returns me a row like this
> d e NULL
>
> if i use COALESCE it will return me
>
> d e (emptycolumn)
>
> i need to return only two columns which has values ,i dont want the
> column which is null to be returned.
>
> city state
> d e
>
> this should be my result not
>
> city state country
> d e null
>
>
> i think this requirement is clear.

For a single row this is possible by using dynamic SQL, but I am not
going to show it, because while the requirement might be clear, it
does not make sense. You simply don't use SQL this way. It's like
taking the vaccum cleaner to the grocery store to carry the produce back
home with you. You can do it, but everyone would consider you crazy if you
did.

I don't know what your real business requirment is, but you should
probably look for a client-side solution.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9B1AF105990FDYazorman@127.0.0.1...
> raj (2rajesh.b@gmail.com) writes:
>> i think this requirement is clear.
>
> For a single row this is possible by using dynamic SQL, but I am not
> going to show it, because while the requirement might be clear, it
> does not make sense. You simply don't use SQL this way. It's like
> taking the vaccum cleaner to the grocery store to carry the produce back
> home with you. You can do it, but everyone would consider you crazy if you
> did.

Oh wow. You owe me a keyboard! That is great and to the point.

It reminds me of the phrase used by some scientists talk about certain
theories: "Not even wrong."



--
Greg Moore
Ask me about lily, an RPI based CMC.