Optimize the usage of CASE statement in MySQL-CodePudding

Hi all,

I have a table as shown in the screenshot above. I wrote a query using CASE statement so that it will return extra columns that I need. Below is the query that 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 result table that I get:

What I want to achieve in this query is, if the student attended the class, it will show 1 under the column of the class for all the rows belong to that student.

For example, student with student_id 2 attended class 1, so under Class 1 column, both rows for student_id 2 will show 1.

I already achieved what I want in my query, but right now instead of using 1, I want it to be the enrollment_date of the class. Below is the final output that I want:

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

2nd Question:

As you can see in my query above, every class is having one CASE statement respectively in order to create the column for the class. However, there might be Class 5,6,7,... in future, so I need to add in the extra CASE statement again whenever there is different new class exist. Is there anyway that I can optimize my query so that there is no need to have 4 CASE statement for 4 different classes, and yet still can create columns for different classes (when there is a new class, there will be new column for the class 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')
;

CodePudding user response：

Here's an example with both class_end_date and enrollment_date pivoted -

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;

CodePudding user response：

See Pivot for a Stored Procedure that will generate and [optionally] run a query based on the table definition and data.