SQL 列合併經典問題：將形近詞彙聚合為逗號分隔字串

問題情境

給定兩張表：

• originalWord：單字主檔 (ID, Word)
• similarWord：形近詞對應關係 (ID, original_id, similar_id)，雙向儲存（如 101↔102、103↔105、104↔105）

需求：輸出每個單字及其所有形近詞，形近詞以逗號分隔合併為單一欄位。

目標結果

方案一：自定義純量函數（UDF）

CREATE FUNCTION dbo.f_hb(@original_id VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @str VARCHAR(8000) = '';
    SELECT @str = @str + ',' + b.Word
    FROM similarword a
    INNER JOIN originalWord b ON a.similar_id = b.ID
    WHERE a.original_id = @original_id;
    RETURN STUFF(@str, 1, 1, ''); -- 移除首個逗號
END;
GO

-- 調用
SELECT DISTINCT
    a.original_id AS [originalword.ID],
    b.Word AS orig_Word,
    dbo.f_hb(a.original_id) AS similar_id
FROM similarword a
INNER JOIN originalWord b ON a.original_id = b.ID;

優點：語法簡潔、易維護。
缺點：逐行呼叫函數，大量資料時效能較差（RBAR 模式）。

方案二：WHILE 迴圈 + 變數累加

DECLARE @Result TABLE(
    id INT IDENTITY(1,1),
    [originalword.ID] INT,
    orig_word NVARCHAR(30),
    similar_Word NVARCHAR(30)
);
INSERT INTO @Result ([originalword.ID], orig_word)
SELECT ID, Word FROM originalWord;

DECLARE @i INT = 1, @j INT = (SELECT COUNT(*) FROM @Result),
        @similar_Word NVARCHAR(200);

WHILE @i <= @j
BEGIN
    SET @similar_Word = '';
    SELECT @similar_Word = @similar_Word + Word + ','
    FROM (
        SELECT B.Word
        FROM similarword A
        INNER JOIN originalWord B ON A.similar_id = B.ID
        WHERE EXISTS (
            SELECT 1 FROM @Result C
            WHERE C.[originalword.ID] = A.original_id
              AND C.id = @i
        )
    ) Tmp;

    IF @similar_Word <> ''
        UPDATE @Result
        SET similar_Word = SUBSTRING(@similar_Word, 1, LEN(@similar_Word)-1)
        WHERE id = @i;

    SET @i = @i + 1;
END

SELECT [originalword.ID], orig_word, similar_Word FROM @Result;

優點：避免函數呼叫開銷，批次處理。
缺點：程序式寫法，可讀性較低；仍屬逐行處理。

方案三：現代 SQL 標準寫法（SQL Server 2017+ / Azure SQL）

SELECT
    ow.ID AS [originalword.ID],
    ow.Word AS orig_word,
    STRING_AGG(sw.Word, ',') WITHIN GROUP (ORDER BY sw.Word) AS similar_Word
FROM originalWord ow
LEFT JOIN similarWord sw_link ON ow.ID = sw_link.original_id
LEFT JOIN originalWord sw ON sw_link.similar_id = sw.ID
GROUP BY ow.ID, ow.Word
ORDER BY ow.ID;

優點：宣告式、單一掃描、效能最佳、可攜性強（PostgreSQL/MySQL 亦支援 STRING_AGG / GROUP_CONCAT）。

效能比較與建議

建議：優先使用 STRING_AGG（或 FOR XML PATH 相容舊版）；僅在不支援聚合函數的舊版本（≤2014）才考慮 UDF 或迴圈。

延伸應用

• 標籤系統：文章標籤合併顯示
• 多值屬性：產品規格、顏色、尺寸彙總
• 審計日誌：同一交易的多筆操作合併