Transform duplicate rows in columns SQL

Question

I have the following query:

SELECT folders.name folder, s.name `schedule`, ms.month as 'month', concat(s.start, " - ", s.end) `time`,
p.name 'profession', (select count(*) from employees where profession_id = p.id) as "count_employees"
FROM schedules s 
    JOIN folders ON folders.id = s.folder_id
    JOIN month_schedules ms ON ms.schedule_id = s.id
    LEFT JOIN schedule_professions sp ON sp.schedule_id = s.id
    JOIN professions p ON p.id = sp.profession_id
WHERE s.company_id = 82 AND ms.year = 2021 AND ms.month = 2

This query returns the following table

folder	schedule	month	profession	count_employees
folder_a	schedule_a	2	profession_a	3
folder_a	schedule_a	2	profession_b	5
folder_a	schedule_a	2	profession_c	1
folder_a	schedule_b	2	profession_a	1
folder_a	schedule_b	2	profession_b	0
folder_a	schedule_b	2	profession_c	0

but what I want is this:

folder	schedule	month	time	profession_a	profession_b	profession_c
folder_a	schedule_a	2	0	3	5	1
folder_a	schedule_b	2	0	1	0	0

The number of professions is unknown, so has to be dynamically calculated.

I tried using GROUP_CONCAT and CONCAT, but I'm a newbie with SQL, so I didn't have any success.

this is a pivot problem but for that we also nee the data before so make a [mre] — nbk, Feb 22 '21 at 16:54
To clarify, you are saying you don't know the number of profession columns (i.e. the values under `profession` in the first resultset) that will be generated, correct? — Connor Low, Feb 22 '21 at 16:56
yeah, the number of professions can be 0, 3 or 100, so this has to be dynamically — Felipe Silva, Feb 22 '21 at 16:59

score 0 · Answer 1 · answered Feb 22 '21 at 16:56

SELECT 
   folders.name folder, 
   s.name `schedule`, 
   ms.month as 'month', 
   concat(s.start, " - ", s.end) `time`,
   count(CASE WHEN p.name="profession_a" THEN 1 ELSE 0 END) 'profession_a', 
   count(CASE WHEN p.name="profession_b" THEN 1 ELSE 0 END) 'profession_b', 
   count(CASE WHEN p.name="profession_c" THEN 1 ELSE 0 END) 'profession_c', 
   (select count(*) from employees where profession_id = p.id) as "count_employees"
FROM schedules s 
    JOIN folders ON folders.id = s.folder_id
    JOIN month_schedules ms ON ms.schedule_id = s.id
    LEFT JOIN schedule_professions sp ON sp.schedule_id = s.id
    JOIN professions p ON p.id = sp.profession_id
WHERE s.company_id = 82 AND ms.year = 2021 AND ms.month = 2

You only need to check if the GROUP BY is correct, i did not do that check....

yeah this works to a static number of professions, but it can be 1, 2, 100 or more professions, this need to be dynamically — Felipe Silva, Feb 22 '21 at 16:59
A SQL statement cannot generate a set of dynamic columns... You can only do that by creating your SQL statement dynamically (see: https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql ) — Luuk, Feb 22 '21 at 17:04

Transform duplicate rows in columns SQL

1 Answers1