SQL实现仅橙子标识列与水果名称拼接列的方法及优化问询

现有customer_fruits表结构及数据如下：

需要新增两个列：

• only_orange：客户仅拥有橙子时设为1，否则为0
• fruits：将客户拥有的水果名称用英文逗号分隔拼接

目标表如下：

原方法使用临时表#tmp1筛选仅拥有橙子的客户，再左连接实现only_orange列，现寻求更优实现及fruits列的拼接方案。

1. 优化only_orange列的实现（无需临时表） #

直接通过条件判断计算该列，无需创建临时表，效率更高：

SELECT 
    customer1,
    apple,
    pear,
    grape,
    orange,
    CASE 
        WHEN apple = 0 AND pear = 0 AND grape = 0 AND orange = 1 THEN 1
        ELSE 0
    END AS only_orange
FROM customer_fruits

2. 实现fruits列的拼接（SQL Server 环境） #

提供两种可行方案，可根据实际场景选择：

方法一：使用UNPIVOT + STRING_AGG （推荐，扩展性强）

通过UNPIVOT将列转成行，再用STRING_AGG拼接水果名称，后续新增水果字段时只需修改字段列表即可：

WITH unpivoted AS (
    SELECT 
        customer1,
        fruit_name
    FROM customer_fruits
    UNPIVOT (
        has_fruit FOR fruit_name IN (apple, pear, grape, orange)
    ) AS up
    WHERE has_fruit = 1
)
SELECT 
    cf.customer1,
    cf.apple,
    cf.pear,
    cf.grape,
    cf.orange,
    CASE 
        WHEN cf.apple = 0 AND cf.pear = 0 AND cf.grape = 0 AND cf.orange = 1 THEN 1
        ELSE 0
    END AS only_orange,
    STRING_AGG(u.fruit_name, ', ') AS fruits
FROM customer_fruits cf
JOIN unpivoted u ON cf.customer1 = u.customer1
GROUP BY cf.customer1, cf.apple, cf.pear, cf.grape, cf.orange
ORDER BY cf.customer1

方法二：条件拼接字符串（适合字段较少的场景）

通过逐个判断字段值拼接字符串，逻辑直观，适合固定少量字段的情况：

SELECT 
    customer1,
    apple,
    pear,
    grape,
    orange,
    CASE 
        WHEN apple = 0 AND pear = 0 AND grape = 0 AND orange = 1 THEN 1
        ELSE 0
    END AS only_orange,
    TRIM(', ' FROM 
        CASE WHEN apple = 1 THEN 'apple, ' ELSE '' END +
        CASE WHEN pear = 1 THEN 'pear, ' ELSE '' END +
        CASE WHEN grape = 1 THEN 'grape, ' ELSE '' END +
        CASE WHEN orange = 1 THEN 'orange' ELSE '' END
    ) AS fruits
FROM customer_fruits

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