SQL Server 2005 資料行拆分與合併：動態 SQL 搭配 FOR XML PATH 實作字串聚合

常見需求：依某鍵值將多筆資料的某欄位合併為單一字串 (如逗號分隔)，或反向拆分。本文提供一個通用預存程序 Helper.joinValue，靈活處理各種合併場景。

預存程式碼

CREATE PROCEDURE [Helper].[joinValue]
    @TableName NVARCHAR(100),      -- 來源資料表
    @KeyColName NVARCHAR(20),      -- 分組鍵值欄位
    @joinColName NVARCHAR(20),     -- 待合併欄位
    @Quote NVARCHAR(10) = N',',    -- 分隔符號
    @where NVARCHAR(MAX) = NULL    -- 額外 WHERE 條件 (不含 WHERE 關鍵字)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX);

    -- 動態建構查詢：先取得不重複鍵值，再 OUTER APPLY 合併對應列
    IF @where IS NULL
        SET @SQL = N'SELECT * FROM (SELECT DISTINCT KeyCol = @KeyColName FROM @TableName) a';
    ELSE
        SET @SQL = N'SELECT * FROM (SELECT DISTINCT KeyCol = @KeyColName FROM @TableName WHERE @where) a';

    SET @SQL = @SQL + N'
    OUTER APPLY (
        SELECT NewValues = STUFF(
            REPLACE(REPLACE(REPLACE((
                SELECT joinCol = @joinColName
                FROM @TableName b
                WHERE @KeyColName = a.KeyCol
                FOR XML RAW
            ), '''', ''''), ''/>''', ''''), 1, LEN(@Quote), @Quote)
    ) c';

    -- 參數化替換
    SET @SQL = REPLACE(@SQL, N'@TableName', @TableName);
    SET @SQL = REPLACE(@SQL, N'@KeyColName', @KeyColName);
    SET @SQL = REPLACE(@SQL, N'@joinColName', @joinColName);
    SET @SQL = REPLACE(@SQL, N'@Quote', @Quote);
    IF @where IS NOT NULL
        SET @SQL = REPLACE(@SQL, N'@where', @where);

    EXEC sp_executesql @SQL;
END

原理解析

• FOR XML RAW 將多列轉為 XML 片段
• 多層 REPLACE 移除 XML 標籤，保留值與分隔符
• STUFF 移除首個多餘分隔符
• 動態 SQL 實現資料表/欄位/條件參數化

使用範例

EXEC Helper.joinValue 'Orders', 'CustomerID', 'ProductName', ',', 'OrderDate > ''2023-01-01''';

輸出每位客戶的產品名稱合併清單。此法在 SQL 2005/2008 無 STRING_AGG 時極為實用。