SQL中如何将宽表拆分行并匹配商品编码后合并至主表？

需求说明 #

• 主表结构：Cust_Nr、Article、Price，不存储客户-商品组合的0或NULL占位行
• 商品编码映射：1111=Oranges、1112=Apples、1113=Lemons
• 需将外部系统提供的宽表（Cust_Nr、Oranges、Apples、Lemons）转换为主表结构，排除价格为0的行
• 实现可重复执行的合并逻辑：更新主表中已存在的客户-商品价格，插入不存在的有效记录

现有问题 #

使用UNPIVOT拆分宽表后，仅得到Cust_Nr和Price字段，缺少对应的Article编码列，且未实现与主表的合并逻辑。

解决方案 #

1. 给拆分结果添加Article列 #

提供两种可行的拆分方式，均会自动排除价格为0的行：

方式一：改进UNPIVOT语句

通过CASE语句将UNPIVOT生成的列名映射为对应的商品编码：

DROP TABLE IF EXISTS #fruits
DROP TABLE IF EXISTS #mytest

CREATE TABLE #fruits
(
  Cust_NR INT,
  Oranges INT,
  Apples INT,
  Lemons INT,
);

INSERT #fruits
  (Cust_Nr, Oranges, Apples, Lemons)
VALUES
  (43,0,4,0),
  (52,5,0,5)

-- 拆分宽表并添加Article列
SELECT 
  Cust_Nr,
  CASE up.Dummy
    WHEN 'Oranges' THEN 1111
    WHEN 'Apples' THEN 1112
    WHEN 'Lemons' THEN 1113
  END AS Article,
  Price
INTO #mytest
FROM
(
  SELECT Cust_Nr, Oranges, Apples, Lemons 
  FROM #fruits
) AS cp
UNPIVOT 
(
  Price FOR Dummy IN (Oranges , Apples, Lemons)
) AS up
WHERE up.Price <> 0;

SELECT * FROM #mytest

方式二：使用UNION ALL拆分（更直观）

直接对每个商品列单独查询并指定对应编码，再合并结果：

DROP TABLE IF EXISTS #fruits
DROP TABLE IF EXISTS #mytest

CREATE TABLE #fruits
(
  Cust_NR INT,
  Oranges INT,
  Apples INT,
  Lemons INT,
);

INSERT #fruits
  (Cust_Nr, Oranges, Apples, Lemons)
VALUES
  (43,0,4,0),
  (52,5,0,5)

-- 拆分宽表并添加Article列
SELECT Cust_Nr, 1111 AS Article, Oranges AS Price INTO #mytest
FROM #fruits WHERE Oranges <> 0
UNION ALL
SELECT Cust_Nr, 1112 AS Article, Apples AS Price
FROM #fruits WHERE Apples <> 0
UNION ALL
SELECT Cust_Nr, 1113 AS Article, Lemons AS Price
FROM #fruits WHERE Lemons <> 0;

SELECT * FROM #mytest

执行后#mytest将包含完整的Cust_Nr、Article、Price结构，且无价格为0的行。

2. 可重复执行的主表合并逻辑 #

使用SQL Server的MERGE语句，可同时处理更新和插入操作，且支持重复执行（需主表有唯一约束确保客户-商品组合唯一）。

步骤1：确保主表结构及约束

-- 创建主表（若已存在则跳过）
CREATE TABLE IF NOT EXISTS MainTable
(
  Cust_Nr INT,
  Article INT,
  Price INT,
  -- 主键约束：确保每个客户-商品组合唯一
  CONSTRAINT PK_MainTable PRIMARY KEY (Cust_Nr, Article)
);

-- 模拟主表已有数据（可选）
INSERT INTO MainTable (Cust_Nr, Article, Price)
VALUES (43, 1112, 3), (52, 1111, 4);

步骤2：执行MERGE合并

-- 合并#mytest数据到主表：更新已有记录，插入新记录
MERGE INTO MainTable AS target
USING #mytest AS source
ON target.Cust_Nr = source.Cust_Nr AND target.Article = source.Article
WHEN MATCHED THEN
  UPDATE SET target.Price = source.Price
WHEN NOT MATCHED THEN
  INSERT (Cust_Nr, Article, Price)
  VALUES (source.Cust_Nr, source.Article, source.Price);

每次执行该语句时，会自动：

• 匹配主表中已存在的Cust_Nr+Article组合，更新其Price
• 插入主表中不存在的有效Cust_Nr+Article组合

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