<p>Hello everyone,</p> <p>I have a table as shown in the screenshot above. I wrote a query using the <code>CASE</code> statement so that it would return the extra columns I needed. Here is the query I wrote: </p> <pre class="brush:php;toolbar:false;">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;</pre> <p>This is the table of results I get: </p> <p>What I want to achieve in this query is that if a student attended the course, it will display <code>1</code> under the class column for all rows belonging to that student. </p> <p>For example, a student with <code>student_id</code> <code>2</code> is in class <code>1</code> and is therefore in class <code>1< Under the ;/code> column, both rows for <code>student_id</code> <code>2</code> will read <code> 1</code>. </p> <p>I have achieved what I want in the query, but now instead of using <code>1</code> I want it to be the <code>enrollment_date</code> of the class. Here is the final output I want: </p> <p>May I know how I should modify the query to get the final output in the screenshot above? </p> <p><strong>Second question:</strong></p> <p>As you can see in the query above, each class has a separate <code>CASE</code> 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 <code>CASE</code> statements again. Is there anyway I can optimize my query so that I don't need to have 4 <code>CASE</code> 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)? </p> <h2>Sample Data</h2> <pre class="brush:php;toolbar:false;">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') ;</pre></p>

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;