GROUP_CONCAT function is used to concatenate multiple rows into a single field in MySQL.
SELECT
student_id,
GROUP_CONCAT(subject_name SEPARATOR ', ')
FROM
student_subject
GROUP BY student_id;
Distinct Operator can be used to avoid duplicates.
SELECT
student_id,
GROUP_CONCAT(distinct subject_name SEPARATOR ', ')
FROM
student_subject
GROUP BY student_id;
Order By Clause can be used to keep the values sort.
SELECT
student_id,
GROUP_CONCAT(distinct subject_name Order By subject_name ASC SEPARATOR ', ')
FROM
student_subject
GROUP BY student_id;
Default length of the GROUP_CONCAT result is 1024 Bytes. You can set it as per your need.
Syntax:
SET group_concat_max_len = maxLength;
Example:
SET group_concat_max_len = 3072;