CODEWITHSUNDEEP
mysqlsql
Shafizadeh
Shafizadeh

Reputation: 10340

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?

Upvotes: 1

Views: 71

Answers (4)

Pham X. Bach
Pham X. Bach

Reputation: 5442

Your table is bad design. If you have type column, you only need the value column, not 3 columns like that.

For your case, if only 1 column is not null, 2 are null then you could use

SELECT id, type,
     COALESCE(red_id, green_id, blue_id)
FROM colors;

Upvotes: 4

Mojtaba
Mojtaba

Reputation: 5002

You already know what to do. A simple change:

SELECT id, type,
         CASE WHEN (type = 1) THEN red_id
              WHEN (type = 2) THEN green_id
              WHEN (type = 3) THEN blue_id
         END AS color_id
  FROM colors
WHERE 1

Upvotes: 2

cableload
cableload

Reputation: 4375

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

Upvotes: 5

FirebladeDan
FirebladeDan

Reputation: 1069

Haven't tested but this seems right: Change your case statement to this*

CASE WHEN type = 1 THEN red_id
          WHEN type = 2 THEN green_id
          WHEN type = 3 THEN blue_id
     END AS color_id

Upvotes: 1

Related Questions