SQL 列轉行彙總實戰：使用純量函數與集合式迴圈解決「形近詞彙合併」問題

問題描述

給定兩張表：

• originalWord (ID, Word)：單字主檔
• similarWord (ID, original_id, similar_id)：形近詞關聯 (雙向存儲，如 101-102 與 102-101 皆存在)

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

-- 期望結果
originalword.ID | orig_word | similar_Word
----------------|-----------|-------------
101             | about     | abound
102             | abound    | about
103             | beard     | beast
104             | boast     | beast
105             | beast     | beard,boast

方案一：純量函數 + 應用端呼叫 (RBAR 模式)

函數定義

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
    JOIN dbo.originalWord b ON a.similar_id = b.ID
    WHERE a.original_id = @original_id;
    SET @str = RIGHT(@str, LEN(@str) - 1);  -- 移除首個逗號
    RETURN @str;
END

查詢呼叫

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
JOIN dbo.originalWord b ON a.original_id = b.ID;

特點與缺點

• ✅ 程式碼簡潔、邏輯封裝、易理解
• ❌ **逐列執行 (RBAR)**：每個原始單字呼叫一次函數，大量資料時效能差
• ❌ 函數內隱含查詢，優化器難以最佳化整體計畫

方案二：集合式暫存表 + WHILE 迴圈 (偽集合模式)

SET NOCOUNT ON;

-- 建立測試資料
CREATE TABLE originalword (id INT, Word NVARCHAR(30));
CREATE TABLE similarWord (ID INT, original_id INT, similar_id INT);
INSERT INTO originalWord VALUES (101,'about'),(102,'abound'),(103,'beard'),(104,'boast'),(105,'beast');
INSERT INTO similarWord VALUES (1,101,102),(2,102,101),(3,103,105),(4,105,103),(5,104,105),(6,105,104);

-- 核心邏輯
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(1) 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
        JOIN originalWord B ON A.original_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;

DROP TABLE originalword, similarWord;

特點與缺點

• ✅ 避免函數呼叫開銷，單一批次完成
• ❌ 仍屬 **逐列迴圈 (WHILE)**，大量資料 (萬列以上) 明顯慢於真正集合式寫法
• ❌ 程式碼冗長、難維護

方案三：現代 SQL Server 最佳實踐 — STRING_AGG (2017+) / FOR XML PATH (2005~2016)

SQL Server 2017+ : STRING_AGG (原生、高效、可排序)

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

SQL Server 2005~2016 : FOR XML PATH + STUFF

SELECT o.ID AS [originalword.ID],
       o.Word AS orig_Word,
       STUFF((
           SELECT ',' + s.Word
           FROM similarWord sw2
           JOIN originalWord s ON sw2.similar_id = s.ID
           WHERE sw2.original_id = o.ID
           ORDER BY s.Word
           FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS similar_Word
FROM originalWord o;

效能對比總結 (萬筆資料量級測試參考)

選型建議

1. SQL Server 2017+：**首選 STRING_AGG**，原生支援、效能最佳、語法最簡潔
2. SQL Server 2005~2016：使用 FOR XML PATH + STUFF 模式，已證實為最穩健的集合式替代方案
3. 避免：純量函數用於大量資料彙總、WHILE 迴圈處理集合邏輯
4. 特殊場景：若需在舊版本 (2000) 運行，才考慮函數/迴圈，並建議將彙總結果持久化至資料表

總結

「列轉行字串彙總」是關聯式資料庫常見需求。隨版本演進，SQL Server 已提供原生高效解決方案 (STRING_AGG)。理解各版本最佳實踐，並堅持**集合式思維**，是寫出高效能、可維護 SQL 的關鍵。本文三種方案完整展示了從程序式 → 偽集合 → 真集合的演進路徑，供不同版本環境參考選用。