Trying to pluck row values and display as column values with multiple where clauses in MySQL

Question

I have the following query:

SELECT id, user_uid, charity_id, value FROM donations
WHERE charity_id IN (178, 25, 209, 150, 176)
AND year = 2022
ORDER BY value DESC

Which outputs the following table:

id	user_uid	charity_id	value
267558	1a36d6c1-3eca-4a23-a3c7-2827acca3397	178	905
267552	1a36d6c1-3eca-4a23-a3c7-2827acca3397	25	630
92944	03661b7a-56d0-43e1-9d5e-3f913cf8fefd	178	575
...
271876	a620d031-c947-4513-b307-7bafb526789a	150	305
95444	03661b7a-56d0-43e1-9d5e-3f913cf8fefd	176	300

But I'm trying to output it in the following format:

user_uid	charity_178	charity_25	charity_209	charity_150	charity_176
1a36d6c1-3eca-4a23-a3c7-2827acca3397	905	630	NULL	NULL	NULL
03661b7a-56d0-43e1-9d5e-3f913cf8fefd	575	NULL	NULL	NULL	300
...
a620d031-c947-4513-b307-7bafb526789a	NULL	30	NULL	305	NULL

I've tried the following, which I thought should work...

SELECT
    fun.uid,
    d1.value AS 'charity_178',
    d2.value AS 'charity_209',
    d3.value AS 'charity_25',
    d4.value AS 'charity_150',
    d5.value AS 'charity_176'
FROM `fundraisers` AS fun
    JOIN `donations` AS d1 ON fun.uid = d1.user_uid
    JOIN `donations` AS d2 ON fun.uid = d2.user_uid
    JOIN `donations` AS d3 ON fun.uid = d3.user_uid
    JOIN `donations` AS d4 ON fun.uid = d4.user_uid
    JOIN `donations` AS d5 ON fun.uid = d5.user_uid
WHERE d1.year = 2022
    AND d1.charity = 178
    AND d2.year = 2022
    AND d2.charity = 209
    AND d3.year = 2022
    AND d3.charity = 25
    AND d4.year = 2022
    AND d4.charity = 150
    AND d5.year = 2022
    AND d5.charity = 176

But it isn't joining anything that is missing or null in the donations table.

So only returns something like this:

user_uid	charity_178	charity_25	charity_209	charity_150	charity_176
2cd3f974-4cc4-4b89-9bad-475321b68c83	200	10	100	340	90

With a lot of users missing.

---

FWIW, I'm actually doing this in a Laravel application, but the dataset is large and trying to manipulate a collection in this format is killing the server, so I'm trying to move the work to the database and using the DB facade with a dynamically created sql string and DB::RAW($sql)

Answer 1

From the question/answer suggested by @user1191247, I can use conditional aggregation.

The following code works in my example:

SELECT user_uid,
    MAX(CASE WHEN charity_id = 178 THEN value END) "charity_178",
    MAX(CASE WHEN charity_id = 209 THEN value END) "charity_209",
    MAX(CASE WHEN charity_id = 25 THEN value END) "charity_25",
    MAX(CASE WHEN charity_id = 150 THEN value END) "charity_150",
    MAX(CASE WHEN charity_id = 176 THEN value END) "charity_176"
FROM donations
GROUP BY user_uid;