MySQL : horizontal to vertical

Question

Here is the original table :

id	type
1	A
1	A
1	B
2	A

Note that the content of type is VARCHAR and therefore can be anything.

I want to get an output like that :

id	A	B
1	2	1
2	1	NULL

How can I achieve that using mySQL ?

EDIT, here is what i tried so far :

SELECT u.id, i.type, count(*) as total
FROM intervention i,
     intervention_user iu,
     user u
WHERE i.id = iu.user_id
  AND iu.user_id = u.id
GROUP BY i.type, u.id;

Possible answer here: https://stackoverflow.com/questions/3392956/sql-how-to-transpose — Pierre François, Jun 10 '21 at 08:56
Does this answer your question? [How to pivot tables in MySQL](https://stackoverflow.com/questions/48857955/how-to-pivot-tables-in-mysql) — Mark Rotteveel, Jun 10 '21 at 09:02
Your sample data and query have nothing to do with each other. — Gordon Linoff, Jun 10 '21 at 11:06

score 0 · Answer 1 · answered Jun 10 '21 at 11:07

You can use conditional aggregation. I prefer 0 to NULL, so:

select id, sum(type = 'A') as a, sum(type = 'B') as b
from original
group by id;

If you want NULL values, one method uses NULLIF():

select id, nullif(sum(type = 'A'), 0) as a,
       nullif(sum(type = 'B'), 0) as b
from original
group by id;

MySQL : horizontal to vertical

1 Answers1