Concatenate Multiple Rows Into One Field MySQL

by

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;