SQL Server通用方式合并多版本键值数据并更新选定值

我完全懂你现在的痛点——用PIVOT把键值对转成结构化数据后，同一ID下有Version1和Version2两行，想要把它们合并成一行，优先保留Version2的所有值，Version2没有的再用Version1的补充，对吧？而且因为键的数量可能变化，不能硬写字段赋值，得用通用的方法。

下面给你一套可行的方案，分步骤来：

1. 先明确你的键值对表结构（模拟示例） #

假设你的表叫KeyValueStore，结构大概是这样：

CREATE TABLE KeyValueStore (
    ID INT,
    Version INT,
    KeyName VARCHAR(50),
    KeyValue VARCHAR(200)
);

-- 插入测试数据
INSERT INTO KeyValueStore VALUES
(1, 1, 'Name', 'Alice'),
(1, 1, 'Age', '30'),
(1, 1, 'City', 'New York'),
(1, 2, 'Name', 'Alice Smith'),
(1, 2, 'Age', '31'),
(2, 1, 'Name', 'Bob'),
(2, 2, 'City', 'London');

2. 合并多版本数据的核心逻辑 #

不用先PIVOT再合并，咱们可以先通过分组+条件聚合的方式直接拿到合并后的结构化数据，再转JSON。核心思路是：对每个ID，每个KeyName，优先取Version=2的值，没有的话 fallback 到Version=1。

静态列的情况（已知所有KeyName） #

如果你的键是固定的，直接写字段：

SELECT 
    ID,
    COALESCE(MAX(CASE WHEN Version=2 AND KeyName='Name' THEN KeyValue END),
             MAX(CASE WHEN Version=1 AND KeyName='Name' THEN KeyValue END)) AS Name,
    COALESCE(MAX(CASE WHEN Version=2 AND KeyName='Age' THEN KeyValue END),
             MAX(CASE WHEN Version=1 AND KeyName='Age' THEN KeyValue END)) AS Age,
    COALESCE(MAX(CASE WHEN Version=2 AND KeyName='City' THEN KeyValue END),
             MAX(CASE WHEN Version=1 AND KeyName='City' THEN KeyValue END)) AS City
FROM KeyValueStore
WHERE Version IN (1,2)
GROUP BY ID
FOR JSON AUTO;

执行后会得到合并后的JSON，比如ID=1的Name是Alice Smith（Version2的值），Age是31，City是New York（Version2没有，用Version1的）；ID=2的Name是Bob（Version2没有，用Version1），City是London（Version2的值）。

动态列的情况（KeyName不固定） #

如果你的键是动态变化的，不能硬写字段，那得用动态SQL来生成条件聚合的语句，再转JSON：

DECLARE @Columns NVARCHAR(MAX);

-- 先获取所有唯一的KeyName
SELECT @Columns = STRING_AGG(
    CONCAT(
        'COALESCE(MAX(CASE WHEN Version=2 AND KeyName=''', KeyName, ''' THEN KeyValue END),',
        'MAX(CASE WHEN Version=1 AND KeyName=''', KeyName, ''' THEN KeyValue END)) AS ', QUOTENAME(KeyName)
    ),
    ', '
)
FROM (SELECT DISTINCT KeyName FROM KeyValueStore) AS Keys;

-- 生成完整的查询语句
DECLARE @Sql NVARCHAR(MAX) = CONCAT(
    'SELECT ID, ', @Columns, '
    FROM KeyValueStore
    WHERE Version IN (1,2)
    GROUP BY ID
    FOR JSON AUTO;'
);

-- 执行动态SQL
EXEC sp_executesql @Sql;

这个方法会自动适配所有存在的KeyName，完全不用硬编码，满足你“值版本可能变化无法直接赋值”的需求。

3. 为什么不用UNION之后再合并？ #

你之前尝试用UNION得到两行数据，其实不如直接在聚合阶段就完成合并——UNION后的两行还要做JOIN或者窗口函数来取优先级，反而更复杂。上面的条件聚合一步到位，效率也更高。

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