T-SQL 字串合併技巧：利用暫存表實現群組內欄位串聯

場景

將同群組的多筆資料合併為單一列，例如：

col1  col2
a     1,2
b     1,2,3

方法一：變數累加（適用固定筆數）

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb;
GO
CREATE TABLE tb(col1 varchar(10), col2 int);
INSERT tb SELECT 'a',null UNION ALL SELECT 'a',null UNION ALL SELECT 'b',null UNION ALL SELECT 'b',null;

DECLARE @col1 varchar(10), @col2 int;
UPDATE tb SET @col2 = CASE WHEN @col1 = col1 THEN @col2 + 1 ELSE 1 END,
               @col1 = col1, col2 = @col2;

-- 每組 <=2 筆合併
SELECT col1,
       col2 = LTRIM(MIN(col2)) + CASE WHEN COUNT(*) = 1 THEN '' ELSE ',' + LTRIM(MAX(col2)) END
FROM tb GROUP BY col1;

-- 每組 <=3 筆合併
INSERT tb SELECT 'b',3;
SELECT col1,
       col2 = CAST(MIN(col2) AS varchar)
            + CASE WHEN COUNT(*) = 3 THEN ',' + CAST((SELECT col2 FROM tb WHERE col1 = a.col1 AND col2 NOT IN (MAX(a.col2), MIN(a.col2))) AS varchar) ELSE '' END
            + CASE WHEN COUNT(*) >= 2 THEN ',' + CAST(MAX(col2) AS varchar) ELSE '' END
FROM tb a GROUP BY col1;

方法二：暫存表 + 變數累加（通用、不限筆數）

IF OBJECT_ID('tempdb..#') IS NOT NULL DROP TABLE #;
GO
SELECT col1, LTRIM(col2) AS col2
INTO # FROM tb ORDER BY col1, col2;

DECLARE @col1 varchar(10), @col2 varchar(20);
UPDATE # SET @col2 = CASE WHEN @col1 = col1 THEN @col2 + ',' + col2 ELSE col2 END,
            @col1 = col1,
            col2 = @col2;
GO

SELECT col1, MAX(col2) AS col2
FROM # GROUP BY col1;
/* 結果：
col1  col2
a     1,2
b     1,2,3
*/

關鍵點

• 先排序再寫入暫存表，確保群組內順序正確
• 利用 UPDATE 配合變數進行行間累加
• 最後 GROUP BY + MAX 取得每組最終合併字串