基于Type值动态映射列：SQL数据转换需求实现

需求说明 #

将包含多组TypeN和ValueN的宽表，按规则转换为标准姓名地址结构：

• 当Type=1时，对应的Value按出现顺序依次映射到LastName、FirstName、MiddleName
• 当Type=2时，对应的Value按出现顺序依次映射到AddressLine1、AddressLine2、AddressLine3
  不同消费者的Type字段数量和类型分布不固定，需实现简洁的转换逻辑。

示例输入表 #

DECLARE @CONSUMER TABLE(
    ConsumerID VARCHAR(20),
    Type1 VARCHAR(1),
    Value1  VARCHAR(100),
    Type2 VARCHAR(1),
    Value2  VARCHAR(100),
    Type3 VARCHAR(1),
    Value3  VARCHAR(100),
    Type4 VARCHAR(1),
    Value4  VARCHAR(100),
    Type5 VARCHAR(1),
    Value5  VARCHAR(100),
    Type6 VARCHAR(1),
    Value6  VARCHAR(100)
)
INSERT INTO @CONSUMER
SELECT '100','1','John','1','Smith','1','M','2','test line1','2','test line2','2','test line3' UNION ALL
SELECT '101','1','John','1','Smith','2','line1','2','line2','2','line3','','' UNION ALL
SELECT '104','1','John','1','Smith','2','test line1','','','','','','' UNION ALL
SELECT '105','1','John','2','test line1','','','','','','','','' UNION ALL
SELECT '106','1','John','1','Smith','2','test line1','2','test line2','','','','' UNION ALL
SELECT '102','1','John','2','test line1','2','test line2','2','test line3','','','','' UNION ALL
SELECT '103','1','John','1','Smith','1','M','2','test line1','2','test line2','',''

简洁实现方案 #

WITH Unpivoted AS (
    -- 将宽表转为窄表，提取所有有效Type和Value对
    SELECT 
        ConsumerID,
        Type,
        Value,
        -- 给每个ConsumerID下的Type=1/Type=2分别编号，用于后续映射到对应字段
        CASE Type
            WHEN '1' THEN ROW_NUMBER() OVER(PARTITION BY ConsumerID, Type ORDER BY (SELECT NULL))
            WHEN '2' THEN ROW_NUMBER() OVER(PARTITION BY ConsumerID, Type ORDER BY (SELECT NULL))
        END AS Seq
    FROM @CONSUMER
    UNPIVOT (
        Type FOR TypeCol IN (Type1, Type2, Type3, Type4, Type5, Type6)
    ) u1
    UNPIVOT (
        Value FOR ValueCol IN (Value1, Value2, Value3, Value4, Value5, Value6)
    ) u2
    -- 匹配Type和Value的对应关系（TypeN对应ValueN）
    WHERE RIGHT(u1.TypeCol,1) = RIGHT(u2.ValueCol,1)
      AND Type IN ('1','2')
      -- 过滤空的Type和Value
      AND ISNULL(Type,'') <> ''
      AND ISNULL(Value,'') <> ''
),
Pivoted AS (
    -- 将窄表转回宽表，按Type和编号映射到目标字段
    SELECT 
        ConsumerID,
        -- 姓名类字段：Type=1的第1/2/3条分别对应LastName/FirstName/MiddleName
        MAX(CASE WHEN Type='1' AND Seq=1 THEN Value END) AS LastName,
        MAX(CASE WHEN Type='1' AND Seq=2 THEN Value END) AS FirstName,
        MAX(CASE WHEN Type='1' AND Seq=3 THEN Value END) AS MiddleName,
        -- 地址类字段：Type=2的第1/2/3条分别对应AddressLine1/AddressLine2/AddressLine3
        MAX(CASE WHEN Type='2' AND Seq=1 THEN Value END) AS AddressLine1,
        MAX(CASE WHEN Type='2' AND Seq=2 THEN Value END) AS AddressLine2,
        MAX(CASE WHEN Type='2' AND Seq=3 THEN Value END) AS AddressLine3
    FROM Unpivoted
    GROUP BY ConsumerID
)
SELECT * FROM Pivoted ORDER BY ConsumerID;

方案说明 #

1. Unpivoted阶段：将宽表的多组TypeN和ValueN转换为多行结构，确保TypeN和ValueN一一对应，过滤无效空值，并给每个消费者下的Type=1、Type=2分别按原表顺序编号。
2. Pivoted阶段：根据Type和编号，将对应Value映射到目标姓名、地址字段，通过MAX()聚合函数确保每个消费者只返回一行结果。

验证结果 #

执行上述SQL后，输出结果与预期完全匹配：

ConsumerID | LastName | FirstName | MiddleName | AddressLine1 | AddressLine2 | AddressLine3
-----------|----------|-----------|------------|--------------|--------------|-------------
100        | John     | Smith     | M          | test line1   | test line2   | test line3
101        | John     | Smith     | NULL       | line1        | line2        | line3
102        | John     | NULL      | NULL       | test line1   | test line2   | test line3
103        | John     | Smith     | M          | test line1   | test line2   | NULL
104        | John     | Smith     | NULL       | test line1   | NULL         | NULL
105        | John     | NULL      | NULL       | test line1   | NULL         | NULL
106        | John     | Smith     | NULL       | test line1   | test line2   | NULL

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