如何在SQL查询中按Config和Product合并同表的Currency列值？

需求：当t_LimitCurrency表中Config与Product列值重复但Currency不同时，将同一组（相同Config+Product）的所有Currency合并到同一行的单个字段中。

你之前尝试的自连接SQL存在问题：这种方式会生成大量重复记录，且仅能两两拼接Currency，无法处理同一组存在多个不同Currency的场景。

以下是针对不同数据库的正确实现方案：

SQL Server（2017及以上版本） #

SELECT 
    Config, 
    Product, 
    STRING_AGG(DISTINCT Currency, ',') AS Currencies
FROM t_LimitCurrency
GROUP BY Config, Product

• 若不需要去重（允许同一组内出现重复Currency值），去掉DISTINCT即可。

MySQL/MariaDB #

SELECT 
    Config, 
    Product, 
    GROUP_CONCAT(DISTINCT Currency SEPARATOR ',') AS Currencies
FROM t_LimitCurrency
GROUP BY Config, Product

• GROUP_CONCAT默认用逗号分隔，如需其他分隔符可修改SEPARATOR后的内容。

Oracle #

-- 无重复Currency的场景
SELECT 
    Config, 
    Product, 
    LISTAGG(Currency, ',') WITHIN GROUP (ORDER BY Currency) AS Currencies
FROM t_LimitCurrency
GROUP BY Config, Product

-- 需要去重的场景
SELECT 
    Config, 
    Product, 
    LISTAGG(Currency, ',') WITHIN GROUP (ORDER BY Currency) AS Currencies
FROM (
    SELECT DISTINCT Config, Product, Currency 
    FROM t_LimitCurrency
)
GROUP BY Config, Product

• WITHIN GROUP (ORDER BY Currency)用于指定Currency的拼接顺序，可按需调整。

PostgreSQL #

SELECT 
    Config, 
    Product, 
    STRING_AGG(DISTINCT Currency, ',' ORDER BY Currency) AS Currencies
FROM t_LimitCurrency
GROUP BY Config, Product

• ORDER BY Currency可选，用于控制拼接后的Currency排序。

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