如何在MariaDB与BigQuery中实现类似Excel转置的行转列功能？

问题场景 #

我正在使用MariaDB连接BigQuery，需要将仓库管理系统中每个商品的多个位置记录转换为单行多列格式，最多保留4个位置。

原始数据结构 #

目标格式 #

错误尝试的SQL #

Select am_p as amsku, sum(CAST(qty as numeric)) as sum, 
 COALESCE(MAX(CASE 
    WHEN il.rn = 1 THEN concat(location, '[', quantity, ']')
 END), '') AS LOC1,
 COALESCE(MAX(CASE 
    WHEN il.rn = 2 THEN concat(location, '[', quantity, ']')
 END), '') AS LOC2,
 COALESCE(MAX(CASE 
    WHEN il.rn = 3 THEN concat(location, '[', quantity, ']')
 END), '') AS LOC3,
 COALESCE(MAX(CASE 
    WHEN il.rn = 4 THEN concat(location, '[', quantity, ']')
 END), '') AS LOC4,
 name, 
 upc
from `inflow_loc.daily_order_for_picking2` as vdp 
LEFT OUTER JOIN (
  SELECT item, location, quantity, ROW_NUMBER() OVER (PARTITION BY item ORDER BY location) AS rn from `inflow_loc.inflow_loc`
  where location NOT LIKE '%ZSEARCH%' and
  quantity NOT LIKE '%-%'
) as il ON vdp.am_p = il.item
group by am_p, location, quantity, name, upc, rn
order by amsku, rn;

错误结果 #

执行后各位置列分散在不同行：

修正后的SQL方案 #

方案1：修正条件聚合的分组逻辑 #

核心问题是原SQL的GROUP BY包含了location、quantity、rn等拆分分组的字段，导致每个位置单独成组。只需按商品维度分组即可：

SELECT 
  vdp.am_p AS amsku,
  SUM(CAST(vdp.qty AS NUMERIC)) AS sum_qty,
  COALESCE(MAX(CASE WHEN il.rn = 1 THEN CONCAT(il.location, '[', il.quantity, ']') END), '') AS LOC1,
  COALESCE(MAX(CASE WHEN il.rn = 2 THEN CONCAT(il.location, '[', il.quantity, ']') END), '') AS LOC2,
  COALESCE(MAX(CASE WHEN il.rn = 3 THEN CONCAT(il.location, '[', il.quantity, ']') END), '') AS LOC3,
  COALESCE(MAX(CASE WHEN il.rn = 4 THEN CONCAT(il.location, '[', il.quantity, ']') END), '') AS LOC4,
  vdp.name,
  vdp.upc
FROM `inflow_loc.daily_order_for_picking2` AS vdp
LEFT JOIN (
  SELECT 
    item, 
    location, 
    quantity, 
    ROW_NUMBER() OVER (PARTITION BY item ORDER BY location) AS rn 
  FROM `inflow_loc.inflow_loc`
  WHERE location NOT LIKE '%ZSEARCH%' 
    AND quantity NOT LIKE '%-%'
) AS il ON vdp.am_p = il.item
GROUP BY vdp.am_p, vdp.name, vdp.upc
ORDER BY amsku;

方案2：使用BigQuery原生PIVOT函数 #

利用BigQuery的PIVOT语法实现更简洁的行转列：

WITH ranked_locations AS (
  SELECT 
    vdp.am_p AS amsku,
    vdp.name,
    vdp.upc,
    CAST(vdp.qty AS NUMERIC) AS qty,
    CONCAT(il.location, '[', il.quantity, ']') AS full_location,
    'LOC' || CAST(il.rn AS STRING) AS loc_col
  FROM `inflow_loc.daily_order_for_picking2` AS vdp
  LEFT JOIN (
    SELECT 
      item, 
      location, 
      quantity, 
      ROW_NUMBER() OVER (PARTITION BY item ORDER BY location) AS rn 
    FROM `inflow_loc.inflow_loc`
    WHERE location NOT LIKE '%ZSEARCH%' 
      AND quantity NOT LIKE '%-%'
      AND rn <= 4 -- 仅保留前4个位置
  ) AS il ON vdp.am_p = il.item
)
SELECT 
  amsku,
  SUM(qty) AS sum_qty,
  name,
  upc,
  COALESCE(LOC1, '') AS LOC1,
  COALESCE(LOC2, '') AS LOC2,
  COALESCE(LOC3, '') AS LOC3,
  COALESCE(LOC4, '') AS LOC4
FROM ranked_locations
PIVOT(
  MAX(full_location) FOR loc_col IN ('LOC1', 'LOC2', 'LOC3', 'LOC4')
)
GROUP BY amsku, name, upc
ORDER BY amsku;

关键修正说明 #

• 移除GROUP BY中location、quantity、rn字段，确保每个商品仅生成一行结果
• 条件聚合通过MAX()函数将同一商品的不同位置值聚合到对应列
• PIVOT方案先标记列名再转列，逻辑更清晰直观

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