CODEWITHSUNDEEP
sqlpostgresql
Caballero
Caballero

Reputation: 12121

How to combine multiple columns into one?

I have the following example table:

| name   | email1          | email2            | email3         | 
.................................................................
| John   | [email protected]  | [email protected]  | [email protected] |
| Jane   | [email protected]  | NULL              | NULL           |

I need a query to combine the email columns in the way that the result would look like this, ignoring the nulls:

| name   | email            |
.............................
| John   | [email protected]   |
| John   | [email protected] | 
| John   | [email protected]   |
| Jane   | [email protected]   |

How can I do this?

Upvotes: 0

Views: 57

Answers (2)

Mureinik
Mureinik

Reputation: 312267

You can union all a series of queries on each email column:

SELECT name, email1
WHERE  email1 IS NOT NULL
UNION ALL
SELECT name, email2
WHERE  email2 IS NOT NULL
UNION ALL
SELECT name, email3
WHERE  email3 IS NOT NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You can use a lateral join:

select t.name, v.email
from t cross join lateral
     (values (email1), (email2), (email3)) v(email)
where v.email is not null;

Upvotes: 2

Related Questions