SQL实现多表多列转置合并的方法求助

要实现这个转置合并的需求，核心是先把每个表的28个列拆成28行（每行带对应序号1-28），再通过Client和序号关联三个表。以下是通用SQL实现方案，兼容大多数关系型数据库：

核心思路 #

每个表通过UNION ALL将多列转为多行，用Seq字段标记序号（1到28），确保同序号的Stock、Consumption、Arrive属于同一组数据，最后通过Client和Seq关联三个转置后的结果。

完整实现代码 #

SELECT 
    a.Client,
    c.Stock,
    a.Consumption,
    b.Arrive
FROM 
    -- 转置Table A（消费数据）
    (
        SELECT Client, 1 AS Seq, Consumption1 AS Consumption FROM TableA
        UNION ALL SELECT Client, 2 AS Seq, Consumption2 AS Consumption FROM TableA
        UNION ALL SELECT Client, 3 AS Seq, Consumption3 AS Consumption FROM TableA
        -- 此处补全4到27的序号对应列，格式与上面一致
        UNION ALL SELECT Client, 28 AS Seq, Consumption28 AS Consumption FROM TableA
    ) a
JOIN 
    -- 转置Table B（到货数据）
    (
        SELECT Client, 1 AS Seq, Arrive1 AS Arrive FROM TableB
        UNION ALL SELECT Client, 2 AS Seq, Arrive2 AS Arrive FROM TableB
        UNION ALL SELECT Client, 3 AS Seq, Arrive3 AS Arrive FROM TableB
        -- 此处补全4到27的序号对应列
        UNION ALL SELECT Client, 28 AS Seq, Arrive28 AS Arrive FROM TableB
    ) b ON a.Client = b.Client AND a.Seq = b.Seq
JOIN 
    -- 转置Table C（库存数据）
    (
        SELECT Client, 1 AS Seq, Stock1 AS Stock FROM TableC
        UNION ALL SELECT Client, 2 AS Seq, Stock2 AS Stock FROM TableC
        UNION ALL SELECT Client, 3 AS Seq, Stock3 AS Stock FROM TableC
        -- 此处补全4到27的序号对应列
        UNION ALL SELECT Client, 28 AS Seq, Stock28 AS Stock FROM TableC
    ) c ON a.Client = c.Client AND a.Seq = c.Seq

优化与注意事项 #

• 如果存在Client在某张表中无对应数据的情况，把JOIN替换为LEFT JOIN，可以保留所有Client的数据（缺失列会显示为NULL）。
• 部分数据库支持更简洁的转置语法，比如SQL Server可以用CROSS APPLY简化：

  -- SQL Server下Table A的简化转置
  SELECT 
      Client,
      Seq,
      Consumption
  FROM TableA
  CROSS APPLY (
      VALUES 
          (1, Consumption1),
          (2, Consumption2),
          -- 补全3到27的行
          (28, Consumption28)
  ) AS CA(Seq, Consumption)
  

  用同样的方式处理Table B和Table C后再关联，代码会更简洁。

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