Use SQL statements to merge multiple rows of data into one row - MaxCompute

Use MaxCompute SQL to merge values from multiple rows into a single row. Two patterns cover most use cases:

String concatenation: use wm_concat to collapse multiple row values into one comma-separated string, grouped by a column.
Conditional aggregation: use CASE WHEN with SUM to count or sum values by category across rows — the standard SQL approach to transforming a narrow table into a wide table.

Sample data

The examples on this page use a table named students:

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

Concatenate row values into a string

Use wm_concat to aggregate values from multiple rows into a single string, grouped by a column.

Syntax:

wm_concat([DISTINCT] '<delimiter>', <column>)

Parameter	Required	Description
`DISTINCT`	No	Removes duplicate values before concatenation
`<delimiter>`	Yes	The separator string placed between concatenated values.
`<column>`	Yes	The column whose values are concatenated.

For the full function reference, see Aggregate functions.

Basic concatenation

Group names by class, concatenating all values with a comma:

SELECT class, wm_concat(',', name) AS names
FROM students
GROUP BY class;

class	names
1	LiLei,HanMM,Jim,HanMM
2	Kate,Peter

Concatenation with deduplication

Add DISTINCT to remove duplicate values before concatenation:

SELECT class, wm_concat(DISTINCT ',', name) AS names
FROM students
GROUP BY class;

class	names
1	LiLei,HanMM,Jim
2	Kate,Peter

Count values by category (conditional aggregation)

Use CASE WHEN with SUM to pivot row values into columns — counting how many rows meet each condition per group.

The following statement counts males and females in each class:

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;

class	cnt_m	cnt_f
1	2	2
2	1	1

To add more categories, include additional SUM(CASE WHEN ...) expressions in the SELECT clause.