如何编写SQL查询实现多字段拼接与行转列聚合得到目标结果

完全可以实现，该需求属于典型的分组聚合字符串拼接+行转列场景，纯SQL即可完成，不需要额外处理。

核心实现逻辑 #

• 第一层分组：按order_id、batch_id、product_id、size统计每个产品对应规格的数量，拼接为数量 x 规格的格式
• 第二层分组：按order_id、batch_id、product_id把同一个产品的所有规格描述拼接为一个字符串
• 行转列处理：通过条件判断把不同product_id的拼接结果放到对应列

示例代码（以MySQL为例） #

SELECT
  order_id,
  batch_id,
  MAX(CASE WHEN product_id = 1 THEN product_spec END) AS product1,
  MAX(CASE WHEN product_id = 5 THEN product_spec END) AS product5
FROM (
  -- 第二层：按产品聚合所有规格描述
  SELECT
    order_id,
    batch_id,
    product_id,
    GROUP_CONCAT(CONCAT(cnt, ' x ', size) SEPARATOR ', ') AS product_spec
  FROM (
    -- 第一层：统计每个产品对应规格的数量
    SELECT
      order_id,
      batch_id,
      product_id,
      size,
      COUNT(*) AS cnt
    FROM 你的表名
    GROUP BY order_id, batch_id, product_id, size
  ) AS t1
  GROUP BY order_id, batch_id, product_id
) AS t2
GROUP BY order_id, batch_id

其他数据库适配修改点 #

如果使用其他数据库，仅需要替换字符串拼接函数即可：

• PostgreSQL / 高版本SQL Server：将GROUP_CONCAT(CONCAT(cnt, ' x ', size) SEPARATOR ', ')替换为STRING_AGG(CONCAT(cnt, ' x ', size), ', ')
• Oracle：替换为LISTAGG(CONCAT(cnt, ' x ', size), ', ') WITHIN GROUP (ORDER BY size)
• 低版本SQL Server：需使用FOR XML PATH语法实现字符串拼接，逻辑和上述方案一致

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