MaxCompute:Merge multiple rows of data into one row

Sample data

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) as names 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	2
  2	1	1