Optimize the use of CASE statements in MySQL

Question

Hello everyone,

I have a table as shown in the screenshot above. I wrote a query using the CASE statement so that it would return the extra columns I needed. Here is the query I wrote:

SELECT
    *,
    CASE WHEN (SUM(CASE WHEN class = 'class 1' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0 END AS 'Class 1',
    CASE WHEN (SUM(CASE WHEN class = 'class 2' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0 END AS 'Class 2',
    CASE WHEN (SUM(CASE WHEN class = 'class 3' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0 END AS 'Class 3',
    CASE WHEN (SUM(CASE WHEN class = 'class 4' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0 END AS 'Class 4'
FROM qa;

This is the table of results I get:

What I want to achieve in this query is that if a student attended the course, it will display 1 under the class column for all rows belonging to that student.

For example, a student with student_id 2 is in class 1 and is therefore in class 1< Under the ;/code> column, both rows for student_id 2 will read 1.

I have achieved what I want in the query, but now instead of using 1 I want it to be the enrollment_date of the class. Here is the final output I want:

May I know how I should modify the query to get the final output in the screenshot above?

Second question:

As you can see in the query above, each class has a separate CASE statement to create columns for that class. However, in the future there may be 5,6,7,... classes, so whenever there are different new classes, I will need to add additional CASE statements again. Is there anyway I can optimize my query so that I don't need to have 4 CASE statements for 4 different classes, but still be able to create columns for different classes (when there is a new class , there will be new columns) classes as well)?

Sample Data

create table qa(
    student_id INT,
    class varchar(20),
    class_end_date date,
    enrollment_date date
);

insert into qa (student_id, class, class_end_date, enrollment_date)
values
(1, 'class 1', '2022-03-03', '2022-02-14'),
(1, 'class 3', '2022-06-13', '2022-04-12'),
(1, 'class 4', '2022-07-03', '2022-06-19'),
(2, 'class 1', '2023-03-03', '2022-07-14'),
(2, 'class 2', '2022-08-03', '2022-07-17'),
(4, 'class 4', '2023-03-03', '2022-012-14'),
(4, 'class 2', '2022-04-03', '2022-03-21')
;

Answer 1

See Pivot for stored procedures that will generate and [optionally] run queries based on table definitions and data.

Answer 2

This is an example that contains both class_end_date and enrollment_date -

SELECT 
    student_id,
    GROUP_CONCAT(IF(class = 'class 1', enrollment_date, null)) 'Class 1 Enrolled',
    GROUP_CONCAT(IF(class = 'class 1', class_end_date, null)) 'Class 1 End',
    GROUP_CONCAT(IF(class = 'class 2', enrollment_date, null)) 'Class 2 Enrolled',
    GROUP_CONCAT(IF(class = 'class 2', class_end_date, null)) 'Class 2 End',
    GROUP_CONCAT(IF(class = 'class 3', enrollment_date, null)) 'Class 3 Enrolled',
    GROUP_CONCAT(IF(class = 'class 3', class_end_date, null)) 'Class 3 End',
    GROUP_CONCAT(IF(class = 'class 4', enrollment_date, null)) 'Class 4 Enrolled',
    GROUP_CONCAT(IF(class = 'class 4', class_end_date, null)) 'Class 4 End'
FROM qa
GROUP BY student_id;