Shafizadeh Shafizadeh - 6 months ago 15
MySQL Question

How do I merge rows in MySQL?

I have a table like this:

// colors
+----+-------+--------+----------+---------+
| id | type | red_id | green_id | blue_id |
+----+-------+--------+----------+---------+
| 1 | 1 | 51 | NULL | NULL |
| 2 | 3 | NULL | NULL | 12 |
| 3 | 1 | 423 | NULL | NULL |
| 4 | 2 | NULL | 5432 | NULL |
| 5 | 2 | NULL | 10 | NULL |
+----+-------+--------+----------+---------+
// ^ type: there is either 1 or 2 or 3 which specifies which color isn't NULL


And I want this output:

// new_colors
+----+-------+----------+
| id | type | color_id |
+----+-------+----------+
| 1 | 1 | 51 |
| 2 | 3 | 12 |
| 3 | 1 | 423 |
| 4 | 2 | 5432 |
| 5 | 2 | 10 |
+----+-------+----------+


I think I have to use
CASE .. WHEN
function.

SELECT id, type,
CASE WHEN red_id IS NOT NULL THEN red_id
WHEN green_id IS NOT NULL THEN green_id
WHEN blut_id IS NOT NULL THEN blue_id
END AS color_id
FROM colors
WHERE 1


But I'm trying to write a query depend on the
type
column. I want to use one of those three numbers
1
,
2
,
3
in the
type
column instead of that
CASE .. WHEN
. Is that possible?

Answer

You want to use COALESCE function in mysql. Coalesce function returns the first non-null value from the list of columns passed to this function as a parameter.

select id, type,coalesce(red_id,green_id,blue_id) as color_id from colors