TSQL一对多关系下同一行多更新：占位符替换查询需求

刚好碰到过类似的动态占位符替换需求，尤其是没法提前知道每条消息有多少个占位符的时候，确实得动点脑筋。下面给你分享两种实用的TSQL解决方案，你可以根据自己的SQL Server版本来选：

首先先创建示例表方便测试（你可以替换成自己的实际表名）：

CREATE TABLE #MessageData (
    MessageID INT,
    Message NVARCHAR(MAX),
    TextID INT,
    Text NVARCHAR(MAX)
);

INSERT INTO #MessageData VALUES
(1,'First name:{0}; Last name:{1}', 0, 'John'),
(1,'First name:{0}; Last name:{1}', 1, 'Doe'),
(2,'Address: {0}; City: {1}; Country: {2}',0,'Maple Str. 1'),
(2,'Address: {0}; City: {1}; Country: {2}',1,'New York'),
(2,'Address: {0}; City: {1}; Country: {2}',2,'USA');

方法一：递归CTE逐次替换（兼容SQL Server 2008及以上） #

这种方法不需要动态SQL，靠递归CTE逐个替换每个占位符，兼容性拉满，适合旧版本的SQL Server：

WITH MessageCTE AS (
    -- 先按MessageID分组，拿到原始消息和排序后的文本值拼接串
    SELECT 
        MessageID,
        MAX(Message) AS OriginalMessage,
        STRING_AGG(Text, '|||') WITHIN GROUP (ORDER BY TextID) AS TextValues
    FROM #MessageData
    GROUP BY MessageID
),
RecursiveReplace AS (
    -- 第一步：先替换{0}占位符
    SELECT 
        MessageID,
        OriginalMessage,
        TextValues,
        CAST(REPLACE(OriginalMessage, '{0}', PARSENAME(REPLACE(TextValues, '|||', '.'), LEN(TextValues) - LEN(REPLACE(TextValues, '|||', '')) + 1)) AS NVARCHAR(MAX)) AS UpdatedMessage,
        1 AS CurrentPlaceholder
    FROM MessageCTE
    WHERE CHARINDEX('{0}', OriginalMessage) > 0

    UNION ALL

    -- 递归替换后续的{1}、{2}...直到没有占位符
    SELECT 
        r.MessageID,
        r.OriginalMessage,
        r.TextValues,
        CAST(REPLACE(r.UpdatedMessage, '{' + CAST(r.CurrentPlaceholder AS VARCHAR) + '}', PARSENAME(REPLACE(r.TextValues, '|||', '.'), LEN(r.TextValues) - LEN(REPLACE(r.TextValues, '|||', '')) + 1 - r.CurrentPlaceholder)) AS NVARCHAR(MAX)) AS UpdatedMessage,
        r.CurrentPlaceholder + 1 AS CurrentPlaceholder
    FROM RecursiveReplace r
    WHERE CHARINDEX('{' + CAST(r.CurrentPlaceholder AS VARCHAR) + '}', r.UpdatedMessage) > 0
)
-- 取每个MessageID的最终替换结果（递归到没有剩余占位符的那一行）
SELECT 
    MessageID,
    UpdatedMessage AS FinalMessage
FROM RecursiveReplace r
WHERE NOT EXISTS (
    SELECT 1 FROM RecursiveReplace r2 
    WHERE r2.MessageID = r.MessageID AND r2.CurrentPlaceholder > r.CurrentPlaceholder
)
-- 如果占位符数量超过100，加上这句解除递归限制：OPTION(MAXRECURSION 0);

方法二：动态SQL结合FORMATMESSAGE（SQL Server 2012+推荐） #

这种方法利用SQL Server原生的FORMATMESSAGE函数，代码更简洁，性能也更好，适合新版本的SQL Server。需要注意的是要转义消息里的单引号和%号，避免语法错误：

DECLARE @SQL NVARCHAR(MAX);

-- 动态构建每个MessageID的FORMATMESSAGE执行语句
SELECT @SQL = STRING_AGG(
    N'SELECT ' + CAST(MessageID AS VARCHAR) + ' AS MessageID, FORMATMESSAGE(''' + 
    -- 转义单引号和%号，避免语法错误
    REPLACE(REPLACE(MAX(Message), '''', ''''''), '%', '%%') + ''', ' + 
    -- 把对应Text按TextID排序拼接成参数列表
    STRING_AGG(QUOTENAME(Text, ''''), ', ') WITHIN GROUP (ORDER BY TextID) + 
    ') AS FinalMessage',
    N' UNION ALL '
)
FROM #MessageData
GROUP BY MessageID;

-- 执行动态生成的SQL
EXEC sp_executesql @SQL;

最终输出结果 #

两种方法都会得到如下结果：

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