Joining multiple rows with same ID in one

Question

I am having trouble with an SQL query. I have two tables.

My first table:

+------------+-------------+---------------+
| id_mission | Some column | Other column  |
+------------+-------------+---------------+
|      1     |     ...     |      ...      |
|      2     |     ...     |      ...      |
+------------+-------------+---------------+

My second table:

+------------+-------------+---------+
| id_mission | id_category | points  |
+------------+-------------+---------+
|          1 |           1 |       3 |
|          1 |           2 |       4 |
|          1 |           3 |       4 |
|          1 |           4 |       8 |
|          2 |           1 |      -4 |
|          2 |           2 |       3 |
|          2 |           3 |       1 |
|          2 |           4 |      -7 |
+------------+-------------+---------+

And I would like to have this kind of result with my SELECT request

+------------+-------------+--------------+---------------+----------------+
| id_mission | Some column | Other column | id_category 1 | id_category X  |
+------------+-------------+--------------+---------------+----------------+
|          1 |         ... |          ... |           ... |            ... |
|          2 |         ... |          ... |           ... |            ... |
+------------+-------------+--------------+---------------+----------------+

I have tried this with the first two column but it doesn't work, I also tried GROUP_CONCAT, it works but it's not the result I want.

SELECT m.id_mission ,mc.id_category 1,mc1.id_category 2
from mission m 
left join mission_category mc on m.id_mission = mc.id_mission 
left join mission_category mc1 on m.id_mission = mc1.id_mission

Can someone help me?

Your description of the result set is identical to the first table. More detail and explanation is needed. — Gordon Linoff, Sep 07 '20 at 13:46
thta is a simple example how to build pivot table https://stackoverflow.com/a/62712027/5193536 if you need help you must provide example data and not only ---- — nbk, Sep 07 '20 at 13:50

score 2 · Accepted Answer · answered Sep 07 '20 at 13:54

You can use conditional aggregation. Assuming that you want to pivot the points value per category:

select 
    t1.*,
    max(case when t2.id_category = 1 then points end) category_1,
    max(case when t2.id_category = 2 then points end) category_2,
    max(case when t2.id_category = 3 then points end) category_3
from t1
inner join t2 on t2.id_mission = t1.id_mission
group by t1.id_mission

This assumes that id_mission is the primary key of t1 (else, you need to enumerate the columns you want in both the select and group by clauses).

Joining multiple rows with same ID in one

1 Answers1