selecting rows from a table

raj

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

Sep 15 '08 #1

Plamen Ratchev

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

Sep 15 '08 #2

raj

On Sep 15, 12:33*pm, Plamen Ratchev <Pla...@SQLStudio.comwrote:

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

Sep 15 '08 #3

Ed Murphy

raj wrote:

On Sep 15, 12:33 pm, Plamen Ratchev <Pla...@SQLStudio.comwrote:

>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.

Sep 15 '08 #4

raj

On Sep 15, 3:01*pm, Ed Murphy <emurph...@socal.rr.comwrote:

raj wrote:

On Sep 15, 12:33 pm, Plamen Ratchev <Pla...@SQLStudio.comwrote:

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..:-)

Sep 15 '08 #5

--CELKO--

>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.

Sep 15 '08 #6

Erland Sommarskog

raj (2r*******@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, es****@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

Sep 15 '08 #7

Greg D. Moore \(Strider\)

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

raj (2r*******@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.

Sep 16 '08 #8