您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 字串彙總去重函數：自訂合併多列值並移除重複項

編輯：SQL Server 2005

SQL Server 2005/2008/2012/2014/2016 均無內建字串彙總函數 (SQL 2017+ 才有 STRING_AGG)。本文提供一個純 T-SQL 純量函數 dbo.merge，實現「依群組鍵值彙總字串欄位，以逗號分隔，並移除重複值」的功能，適用於舊版本遷移、相容性需求場景。

需求場景

-- 原始資料
test_id | test_value
--------|-----------
a       | 01,03,04
a       | 02,04
b       | 03,04,08
b       | 06,08,09
c       | 09
c       | 10

-- 期望結果
test_id | test_value
--------|------------------
a       | 01,03,04,02
b       | 03,04,08,06,09
c       | 09,10

函數完整程式碼

IF OBJECT_ID('dbo.merge', 'FN') IS NOT NULL DROP FUNCTION dbo.merge;
GO

CREATE FUNCTION dbo.merge(@test_id VARCHAR(50))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @s VARCHAR(8000) = '';

    -- 1. 字串彙總：將同群組所有 test_value 串接
    SELECT @s = @s + test_value + ','
    FROM test_a
    WHERE test_id = @test_id;

    -- 2. 去除重複值：逐一解析逗號分隔值，建立不重複結果字串
    DECLARE @value VARCHAR(8000), @result VARCHAR(8000) = '';

    WHILE CHARINDEX(',', @s) <> 0
    BEGIN
        -- 取第一個逗號前的值
        SET @value = LEFT(@s, CHARINDEX(',', @s) - 1);

        -- 若結果字串中不存在該值 (以 ',值,' 形式檢查，避免部分比對)
        IF CHARINDEX(',' + @value + ',', ',' + @result) = 0
            SET @result = @result + @value + ',';

        -- 剔除已處理的第一個值及其逗號，繼續迴圈
        SET @s = RIGHT(@s, LEN(@s) - CHARINDEX(',', @s));
    END

    -- 移除結尾逗號
    SET @s = LEFT(@result, LEN(@result) - 1);

    RETURN @s;
END
GO

測試執行

-- 建立測試表
IF OBJECT_ID('test_a', 'U') IS NOT NULL DROP TABLE test_a;
CREATE TABLE test_a (test_id VARCHAR(50), test_value VARCHAR(50));

INSERT test_a VALUES
('a','01,03,04'), ('a','02,04'),
('b','03,04,08'), ('b','06,08,09'),
('c','09'), ('c','10');

-- 執行彙總去重
SELECT test_id, test_value = dbo.merge(test_id)
FROM test_a
GROUP BY test_id;

結果

test_id test_value
a       01,03,04,02
b       03,04,08,06,09
c       09,10

效能與限制分析

維度	評估
時間複雜度	O(N²) — 迴圈內 `CHARINDEX` 掃描結果字串，資料量大時極慢
最大長度	`VARCHAR(8000)` 限制，超長字串會截斷
適用資料量	群組內 < 100 個值、總長度 < 4000 字元
平行執行	純量函數會強制序列執行，抑制平行計畫

現代化替代方案 (依版本選擇)

SQL Server 2017+ : STRING_AGG (原生、高效、支援 DISTINCT)

SELECT test_id, STRING_AGG(DISTINCT value, ',') WITHIN GROUP (ORDER BY value)
FROM (
    SELECT test_id, value FROM test_a
    CROSS APPLY STRING_SPLIT(test_value, ',')
) t
GROUP BY test_id;

SQL Server 2016/2017 (無 STRING_AGG) : FOR XML PATH + DISTINCT

SELECT 
    test_id,
    STUFF((
        SELECT DISTINCT ',' + value
        FROM test_a a2
        CROSS APPLY STRING_SPLIT(a2.test_value, ',')
        WHERE a2.test_id = a1.test_id
        ORDER BY value
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS merged
FROM test_a a1
GROUP BY test_id;