All Products
Search
Document Center

MaxCompute:Merge multiple rows of data into one row

Last Updated:Oct 17, 2023

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

F

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) 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