This topic describes how to use SQL statements to merge multiple rows of data into one row.

Sample data

class gender name
1 M LiLei
1 F HanMM
1 M Jim
1 M HanMM
2 F Kate
2 M Peter

Examples

  • Example 1: Execute the following statement to merge the rows whose values in the class column are the same into one row based on the values in the name column and deduplicate the values in the name column. You can implement the deduplication by using nested subqueries.
    SELECT class, wm_concat(distinct ',', name) FROM students GROUP BY class;
    Note The wm_concat function is used to aggregate data. For more information, see Aggregate functions.
    The following result is returned.
    class names
    1 LiLei,HanMM,Jim
    2 Kate,Peter
  • Example 2: Execute the following statement to collect statistics on the numbers of males and females based on the values in the class column:
    SELECT 
    class
    ,SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS cnt_m
    ,SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS cnt_f
    FROM students
    GROUP BY class;
    The following result is returned.
    class cnt_m cnt_f
    1 2 1
    2 1 1