SQL Server 多行合併字串實戰：自訂函數 + 應用查詢

範例資料

id  type    productCode
1   鉛筆    0001
2   鉛筆    0002
3   鉛筆    0003
4   鋼筆    0004
5   鋼筆    0005
6   鋼筆    0004
7   圓珠筆  0007
8   圓珠筆  0008
9   圓珠筆  0007

自訂純量函數

CREATE FUNCTION dbo.fn_ConcatProductCode(@type NVARCHAR(10))
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @result NVARCHAR(200) = '';
    DECLARE @code NVARCHAR(200);
    
    SELECT @code = COALESCE(@code + ',', '') + productCode
    FROM T 
    WHERE type = @type
    GROUP BY productCode;  -- 去重
    
    RETURN @code;
END;

應用查詢

SELECT type, dbo.fn_ConcatProductCode(type) AS codes
FROM (SELECT DISTINCT type FROM T) AS A;

結果

type    codes
鋼筆    0004,0005
鉛筆    0001,0002,0003
圓珠筆  0007,0008

補充：SQL Server 2017+ 內建 STRING_AGG

SELECT type, STRING_AGG(productCode, ',') WITHIN GROUP (ORDER BY productCode)
FROM (SELECT DISTINCT type, productCode FROM T) AS A
GROUP BY type;