SQL合并重复行并将指定列拼接为带双引号的逗号分隔字符串

需求说明 #

现有订单表存在多行重复数据，除product name列外其余所有列值均相同。需要合并重复行，将同一分组下的product name字段值拼接为带双引号的逗号分隔字符串。

输入输出示例 #

输入数据 #

Name        address             city    zip     product name
-----------------------------------------------------------------
John Smith  123 e Test Drive    Phoenix 85045   Eureka Copper Canyon, LX 4-Person Tent
John Smith  123 e Test Drive    Phoenix 85045   The North Face Sequoia 4 Tent with Footprint
Tom Test    567 n desert lane   Tempe   86081   Cannondale Trail 5 Bike - 2021

期望输出数据 #

Name        address             city    zip     product name
------------------------------------------------------------------
John Smith  123 e Test Drive    Phoenix 85045   "Eureka Copper Canyon, LX 4-Person Tent", "The North Face Sequoia 4 Tent with Footprint"
Tom Test    567 n desert lane   Tempe   86081   Cannondale Trail 5 Bike - 2021

各数据库实现语句 #

MySQL 8.0+ / MariaDB #

SELECT 
    Name,
    address,
    city,
    zip,
    GROUP_CONCAT(CONCAT('"', `product name`, '"') SEPARATOR ', ') AS `product name`
FROM 你的实际表名
GROUP BY Name, address, city, zip;

PostgreSQL #

SELECT 
    Name,
    address,
    city,
    zip,
    STRING_AGG(CONCAT('"', "product name", '"'), ', ') AS "product name"
FROM 你的实际表名
GROUP BY Name, address, city, zip;

SQL Server 2017+ #

SELECT 
    Name,
    address,
    city,
    zip,
    STRING_AGG(CONCAT('"', [product name], '"'), ', ') AS [product name]
FROM 你的实际表名
GROUP BY Name, address, city, zip;

Oracle 11gR2+ #

SELECT 
    Name,
    address,
    city,
    zip,
    LISTAGG(CONCAT('"', "product name", '"'), ', ') WITHIN GROUP (ORDER BY "product name") AS "product name"
FROM 你的实际表名
GROUP BY Name, address, city, zip;

注意事项 #

• 请将语句中的你的实际表名替换为你自己的订单表名
• 列名如果包含空格、特殊字符，需要按照对应数据库的规则添加标识符包裹：MySQL用反引号`、SQL Server用方括号[]、PostgreSQL/Oracle用双引号""

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