SQL实现列值合并：按成员累加拼接活动历史列

需要将包含Member和Campaign字段的表格，转换为新增Pastcampaigns字段的结果表，其中Pastcampaigns需按Member分组，累加拼接当前行及之前所有的Campaign值（按Campaign升序排列）。

原始数据 #

目标结果 #

解决方案 #

1. MySQL 8.0+ #

利用GROUP_CONCAT结合窗口函数实现累积拼接：

SELECT 
    Member,
    Campaign,
    GROUP_CONCAT(Campaign ORDER BY Campaign SEPARATOR ',') OVER (
        PARTITION BY Member 
        ORDER BY Campaign 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Pastcampaigns
FROM your_table_name
ORDER BY Member, Campaign;

2. PostgreSQL #

使用STRING_AGG窗口函数，注意数值类型需转换为字符串：

SELECT 
    Member,
    Campaign,
    STRING_AGG(Campaign::TEXT, ',' ORDER BY Campaign) OVER (
        PARTITION BY Member 
        ORDER BY Campaign 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Pastcampaigns
FROM your_table_name
ORDER BY Member, Campaign;

3. SQL Server #

版本2022+（支持窗口版STRING_AGG）

SELECT 
    Member,
    Campaign,
    STRING_AGG(Campaign, ',' ORDER BY Campaign) OVER (
        PARTITION BY Member 
        ORDER BY Campaign 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Pastcampaigns
FROM your_table_name
ORDER BY Member, Campaign;

版本2017-2019（用关联子查询实现）

SELECT 
    t1.Member,
    t1.Campaign,
    STRING_AGG(t2.Campaign, ',') WITHIN GROUP (ORDER BY t2.Campaign) AS Pastcampaigns
FROM your_table_name t1
JOIN your_table_name t2 
    ON t1.Member = t2.Member 
    AND t2.Campaign <= t1.Campaign
GROUP BY t1.Member, t1.Campaign
ORDER BY t1.Member, t1.Campaign;

4. Oracle #

使用LISTAGG窗口函数：

SELECT 
    Member,
    Campaign,
    LISTAGG(Campaign, ',') WITHIN GROUP (ORDER BY Campaign) OVER (
        PARTITION BY Member 
        ORDER BY Campaign 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Pastcampaigns
FROM your_table_name
ORDER BY Member, Campaign;

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