如何在Excel或SQL中实现物料UOM与Qty的行转列操作？

Excel 快捷实现方法 #

1. 数据透视表法（快速生成结构化结果） #

• 选中数据源区域，点击「插入」→「数据透视表」，指定放置位置。
• 透视表字段配置：
  • 物料字段拖至「行」区域
  • UOM字段拖至「列」区域
  • Qty字段拖至「值」区域，值计算方式设为「求和」（若同一物料+UOM有多条记录）
• 调整格式后，自动生成每个物料对应各UOM数量的单行展示表。

2. TEXTJOIN 函数法（自定义拼接格式） #

假设物料列是A列，UOM列B列，Qty列C列，在D2单元格输入公式（Excel 365/2021直接回车，旧版本按Ctrl+Shift+Enter确认）：

=TEXTJOIN(", ", TRUE, IF(A$2:A$100=A2, B2&":"&C2, ""))

• 逻辑：匹配当前行物料，将对应UOM和Qty拼接为「UOM:Qty」格式，再用逗号分隔合并到同一单元格。
• 下拉填充公式完成所有物料的整合。

3. Power Query法（适配大数据量） #

• 选中数据源，点击「数据」→「从表格/区域」进入Power Query编辑器。
• 选中物料列，点击「转换」→「分组依据」，分组列选物料，新列名设为UOM_Qty，操作选「所有行」。
• 添加自定义列，输入公式：

Text.Combine(List.Transform([UOM_Qty], each [UOM] & ":" & Text.From([Qty])), ", ")

• 删除冗余列，关闭并上载数据，得到单行整合结果。

SQL 快捷实现方法 #

根据不同数据库的字符串聚合函数，对应写法如下：

MySQL/MariaDB #

SELECT 
    material_code,
    GROUP_CONCAT(CONCAT(uom, ':', qty) SEPARATOR ', ') AS uom_qty
FROM 
    your_table
GROUP BY 
    material_code;

SQL Server 2017+ #

SELECT 
    material_code,
    STRING_AGG(CONCAT(uom, ':', qty), ', ') AS uom_qty
FROM 
    your_table
GROUP BY 
    material_code;

Oracle 11g+ #

SELECT 
    material_code,
    LISTAGG(CONCAT(uom, ':', qty), ', ') WITHIN GROUP (ORDER BY uom) AS uom_qty
FROM 
    your_table
GROUP BY 
    material_code;

PostgreSQL #

SELECT 
    material_code,
    STRING_AGG(CONCAT(uom, ':', qty), ', ') AS uom_qty
FROM 
    your_table
GROUP BY 
    material_code;

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