CODEWITHSUNDEEP
sql
Gil
Gil

Reputation: 21

Get none null column row and other row when its null

I have a problem and I wondered if I can do it only with sql.

So I have a table named: tbl, and columns a, b, c which a and b rows aren't null and c can be null, for example:

a b c
a1 b1 NULL
a2 b2 c2
a3 b3 NULL

And I wish to get the result with columns a and x when x is defined: take from b when c is null and take from c if its not null, so the output will be:

a col2
a1 b1
a2 c2
a3 b3

There is a sql command which can do the above?

Thanks

Upvotes: 0

Views: 35

Answers (3)

Massi FD
Massi FD

Reputation: 398

You can use

select a, nvl(c, b) from table;

Upvotes: 0

edujanini
edujanini

Reputation: 1

If your DBMS does not support the functions from answers above, you can do it with CASE function:

SELECT
  a,
  CASE
    WHEN c IS NULL THEN b
    ELSE c
    END AS col2
FROM
  tbl

Upvotes: 0

lemon
lemon

Reputation: 15482

You can use the COALESCE function, given that your DBMS supports it.

SELECT a, 
       COALESCE(c, b)
FROM tab

Upvotes: 1

Related Questions