CODEWITHSUNDEEP
sql
Prashant Sonar
Prashant Sonar

Reputation: 113

Select only not null column values and display only that values using sql query

Like I have table Like this 

col1  col2  col3  col4
----  ----  ----  ----
   1  NULL  NULL  NULL
NULL     2  NULL  NULL
   3  NULL  NULL  NULL
NULL  NULL  NULL     4

The expected result is:

col5
----
   1
   2
   3
   4

How can I get this kind of Output using SQL query?

Upvotes: 2

Views: 4387

Answers (4)

Mohit Kumar
Mohit Kumar

Reputation: 940

Select * from your_table 
WHERE col1 and col2 and col3 and col4 and col5 IS NOT NULL;

In this approach we can only compare our columns, after that the result will always be true, so I do compare only the fields that can be NOT NULL value in it.

Upvotes: 0

Kunal
Kunal

Reputation: 229

try this

SELECT
    (CASE 
        WHEN col1 IS NOT NULL THEN col1 
        WHEN col2 IS NOT NULL THEN col2
        WHEN col3 IS NOT NULL THEN col3
        WHEN col4 IS NOT NULL THEN col4 
    END) AS col5 FROM table

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Your title suggests :

select t.*
from (select col1 as col5
      from table union all
      select col2 
      from table union all
      . . .
     ) t
where col5 is not null;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271181

You are looking for coalesce(), if you want the first non-NULL value:

select t.*, coalesce(col1, col2, col3, col4) as col5
from t;

Upvotes: 2

Related Questions