求编写SQL：实现按column1分组展示column2的唯一组合映射

原始表格数据如下：

需求：按column 1分组，将每组中column 2的所有唯一值用~拼接成column 2 map字段，最终结果如下：

解决方案 #

MySQL/MariaDB #

使用GROUP_CONCAT函数结合DISTINCT去重，关联回原表：

SELECT 
    t1.`column 1`,
    t1.`column 2`,
    t1.Id,
    t2.`column 2 map`
FROM 
    your_table t1
JOIN (
    SELECT 
        `column 1`,
        GROUP_CONCAT(DISTINCT `column 2` ORDER BY `column 2` SEPARATOR '~') AS `column 2 map`
    FROM 
        your_table
    GROUP BY 
        `column 1`
) t2 ON t1.`column 1` = t2.`column 1`
ORDER BY 
    t1.Id;

PostgreSQL #

使用STRING_AGG函数：

SELECT 
    t1."column 1",
    t1."column 2",
    t1.Id,
    t2."column 2 map"
FROM 
    your_table t1
JOIN (
    SELECT 
        "column 1",
        STRING_AGG(DISTINCT "column 2"::TEXT, '~' ORDER BY "column 2") AS "column 2 map"
    FROM 
        your_table
    GROUP BY 
        "column 1"
) t2 ON t1."column 1" = t2."column 1"
ORDER BY 
    t1.Id;

SQL Server #

使用STRING_AGG函数：

SELECT 
    t1.[column 1],
    t1.[column 2],
    t1.Id,
    t2.[column 2 map]
FROM 
    your_table t1
JOIN (
    SELECT 
        [column 1],
        STRING_AGG(DISTINCT CAST([column 2] AS VARCHAR(MAX)), '~') WITHIN GROUP (ORDER BY [column 2]) AS [column 2 map]
    FROM 
        your_table
    GROUP BY 
        [column 1]
) t2 ON t1.[column 1] = t2.[column 1]
ORDER BY 
    t1.Id;

Oracle（12c及以上） #

使用LISTAGG函数：

SELECT 
    t1."column 1",
    t1."column 2",
    t1.Id,
    t2."column 2 map"
FROM 
    your_table t1
JOIN (
    SELECT 
        "column 1",
        LISTAGG(DISTINCT "column 2", '~') WITHIN GROUP (ORDER BY "column 2") AS "column 2 map"
    FROM 
        your_table
    GROUP BY 
        "column 1"
) t2 ON t1."column 1" = t2."column 1"
ORDER BY 
    t1.Id;

内容的提问来源于stack exchange，提问作者Nithin