How to seperate an enum-value column to each value a bool column in MySQL

August 1, 2022

I have a table:

+-----------+----------+
| articleId | category |
+-----------+----------+
|         1 | life     |
|         1 | game     |
|         2 | tech     |
|         3 | life     |
|       ... | ...      |
+-----------+----------+

Now I have to downgrade the paradigms of data, make them be flat and wide. Because they will be fed into an analysis enviroment which has no such data relationships. (actually, it’s Elasticsearch)

Finally the selected data must be like this:

+-----------+------+------+------+
| articleId | game | life | tech |
+-----------+------+------+------+
|         1 |    1 |    1 |    0 |
|         2 |    0 |    0 |    1 |
|         3 |    0 |    1 |    0 |
|       ... |      |      |      |
+-----------+------+------+------+

Could you tell me how I can do that?

The categories field is enumrable, I can manually create each column.

>Solution :

You need to GROUP BY your articleId and check if your data exists:

SELECT t.`articleId`,
SUM(t.`category`='game') game,
SUM(t.`category`='life') life,
SUM(t.`category`='tech') tech
FROM test_test t 
GROUP BY t.`articleId`

t.category='someText' will either return you 0 or 1 and then using SUM will give you your desired result.