SQL Server 游標處理重複資料合併：Email、OrderNo、TrackingNo 多欄位去重實戰

場景：Email20140725 表含重複郵件記錄，需依 Email, OrderNo, TrackingNo 分組，將同群組的 OrderNo、TrackingNo 以「、」串聯合併為單筆。

游標實作 (逐列處理邏輯)

CREATE PROCEDURE HandleEmailRepeat
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Email VARCHAR(200), @OrderNo VARCHAR(300), @TrackingNo VARCHAR(300);
    DECLARE @PrevEmail VARCHAR(200), @PrevOrderNo VARCHAR(300), @PrevTrackingNo VARCHAR(300);
    DECLARE @MergedOrderNo VARCHAR(MAX), @MergedTrackingNo VARCHAR(MAX);
    
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT Email, OrderNo, TrackingNo 
        FROM Email20140725 
        WHERE [Status] = 0 
        ORDER BY Email, OrderNo, TrackingNo;
    
    OPEN cur;
    FETCH NEXT FROM cur INTO @Email, @OrderNo, @TrackingNo;
    
    -- 初始化第一筆
    SET @PrevEmail = @Email; 
    SET @MergedOrderNo = @OrderNo; 
    SET @MergedTrackingNo = @TrackingNo;
    
    FETCH NEXT FROM cur INTO @Email, @OrderNo, @TrackingNo;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Email = @PrevEmail
        BEGIN
            -- 同群組：累加串聯
            IF @OrderNo NOT LIKE '%' + @MergedOrderNo + '%' -- 簡易去重
                SET @MergedOrderNo += '、' + @OrderNo;
            IF @TrackingNo NOT LIKE '%' + @MergedTrackingNo + '%'
                SET @MergedTrackingNo += '、' + @TrackingNo;
        END
        ELSE
        BEGIN
            -- 群組切換：寫入結果表
            INSERT INTO Email20140725Test (Email, OrderNo, TrackingNo)
            VALUES (@PrevEmail, @MergedOrderNo, @MergedTrackingNo);
            
            -- 重置為新群組
            SET @PrevEmail = @Email;
            SET @MergedOrderNo = @OrderNo;
            SET @MergedTrackingNo = @TrackingNo;
        END
        FETCH NEXT FROM cur INTO @Email, @OrderNo, @TrackingNo;
    END
    
    -- 寫入最後一組
    INSERT INTO Email20140725Test (Email, OrderNo, TrackingNo)
    VALUES (@PrevEmail, @MergedOrderNo, @MergedTrackingNo);
    
    CLOSE cur; DEALLOCATE cur;
END

集合式替代方案 (SQL Server 2017+ STRING_AGG)

INSERT INTO Email20140725Test (Email, OrderNo, TrackingNo)
SELECT 
    Email,
    STRING_AGG(OrderNo, '、') WITHIN GROUP (ORDER BY OrderNo) AS OrderNo,
    STRING_AGG(TrackingNo, '、') WITHIN GROUP (ORDER BY TrackingNo) AS TrackingNo
FROM Email20140725
WHERE [Status] = 0
GROUP BY Email;

SQL Server 2016 及舊版 (FOR XML PATH)

INSERT INTO Email20140725Test (Email, OrderNo, TrackingNo)
SELECT 
    Email,
    STUFF((SELECT '、' + OrderNo FROM Email20140725 t2 WHERE t2.Email = t1.Email FOR XML PATH('')), 1, 1, '') AS OrderNo,
    STUFF((SELECT '、' + TrackingNo FROM Email20140725 t2 WHERE t2.Email = t1.Email FOR XML PATH('')), 1, 1, '') AS TrackingNo
FROM Email20140725 t1
WHERE [Status] = 0
GROUP BY Email;

效能對比

建議：現代版本優先用 STRING_AGG，舊版用 FOR XML PATH，避免游標。僅當邏輯極其複雜 (跨表、多階段、程序性) 才考慮游標。