求助：Oracle按店铺编号合并行并生成指定列的实现方法

可以通过Oracle的字符串聚合函数结合分组统计来实现需求，具体SQL方案如下：

适用Oracle 12c及以上版本（支持LISTAGG带DISTINCT） #

SELECT
    shop_number AS "shop Number",
    LISTAGG(DISTINCT model_type, ' & ') WITHIN GROUP (ORDER BY model_type) AS "Shop Type",
    CASE
        WHEN COUNT(DISTINCT model_type) > 1 THEN 'Mix'
        ELSE MAX(model_type)
    END AS "Shop Type"
FROM
    shop_models
GROUP BY
    shop_number
ORDER BY
    shop_number;

适用Oracle 12c以下版本（先去重再聚合） #

SELECT
    shop_number AS "shop Number",
    LISTAGG(model_type, ' & ') WITHIN GROUP (ORDER BY model_type) AS "Shop Type",
    CASE
        WHEN COUNT(model_type) > 1 THEN 'Mix'
        ELSE model_type
    END AS "Shop Type"
FROM (
    -- 子查询先对每个店铺的型号去重
    SELECT DISTINCT shop_number, model_type
    FROM shop_models
) t
GROUP BY
    shop_number
ORDER BY
    shop_number;

关键逻辑说明 #

• 型号合并：用LISTAGG函数将同一店铺的不同型号按&拼接，DISTINCT确保重复型号只保留一次（比如店铺2的两个Mod-3只会显示一次）
• 类型判断：通过COUNT(DISTINCT model_type)统计店铺的不同型号数量，数量大于1时显示Mix，否则显示唯一的型号值
• 分组排序：按shop_number分组实现行合并，ORDER BY保证结果按店铺编号顺序输出

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