CODEWITHSUNDEEP
sqloracle-database
Bhanuchander Udhayakumar
Bhanuchander Udhayakumar

Reputation: 1646

How to remove the null values and get the table like shown below?

Here i have the oracle db table like shown below:

name  a1     b1     c1
----  ---    ---    ---
a     z      null   null  
a     null   y      null
a     nul    null   z
b     y      z      null
b     null   null   m

So my expected output table is:

name  a1     b1     c1
----  ---    ---    ---
a     z      y      z  
b     y      z      m

Upvotes: 0

Views: 62

Answers (3)

Damini Suthar
Damini Suthar

Reputation: 1492

SELECT name, MAX( a1 ) , MAX( b1 ) , MAX( c1 ) 
FROM Table
GROUP BY name

For SQL this will work

Upvotes: 1

zarruq
zarruq

Reputation: 2465

As aggregate functions [except count(*)] ignores NULL, you can simply use MAX or MIN and group by to get your desired result as below.

SELECT name,
       max(a1) AS a1,
       max(b1) AS b1,
       max(c1) AS c1
FROM table1
GROUP BY name

OR

SELECT name,
       min(a1) AS a1,
       min(b1) AS b1,
       min(c1) AS c1
FROM table1
GROUP BY name

Result:

NAME    A1  B1  C1
------------------
a       z   y   z
b       y   z   m

DEMO

Upvotes: 3

Iłya Bursov
Iłya Bursov

Reputation: 24229

you can try something like that:

select name, max(nvl(a1, '')), max(nvl(a2, '')), max(nvl(a3, ''))
from table
group by name

Upvotes: 2

Related Questions