CODEWITHSUNDEEP
sql
cget
cget

Reputation: 420

How do I combine columns into one, and filter out NULLs?

Here is my table. A list of ids with signup dates in columns newsletter, report, infographics.

enter image description here

I want to combine all those columns into one, without the NULLs

I've tried the following code

SELECT id, combined_column
FROM (
    SELECT id, CONCAT(newsletter, report, infographics) AS combined_column 
    FROM table
)
WHERE combined_column IS NOT NULL

But this just gives me a blank table. Is there a way to solve this? Thanks

Upvotes: 1

Views: 2866

Answers (5)

Vijay Rajendiran
Vijay Rajendiran

Reputation: 496

SELECT id, CONCAT(newsletter, report, infographics) AS combined_column FROM table WHERE newsletter is NOT NULL and report is NOT NULL and infographics is NOT NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Do you just want this?

select max(newsletter) as newsletter,
       max(report) as report,
       max(infographics) as infographics
from t;

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272406

I think you want coalesce which return the first not null value from the list (it you have more than one not null value in a row it'll still return the first one):

SELECT id, COALESCE(newsletter, report, infographics) AS combined_date
FROM t
WHERE COALESCE(newsletter, report, infographics) IS NOT NULL

Upvotes: 3

Sandesh Gupta
Sandesh Gupta

Reputation: 1195

If you are using Oracle, use NVL to replace NULL with empty string

SELECT id, 
   combined_column
FROM (
    SELECT id, 
         CONCAT(NVL(newsletter,''), NVL(report,''), NVL(infographics,'')) AS combined_column 
    FROM table
     )

WHERE combined_column is not NULL

Upvotes: 1

jwismar
jwismar

Reputation: 12268

Answer may depend on what database you're using, so caveat lector.

Is it the case that only one column will be non-null, as in your sample?

Then something like:

SELECT id, COALESCE(newsletter, infographics, report) FROM my_table;

might work for you...

Upvotes: 1

Related Questions