Raws into Strings in Columns - only if unique

问题:

In another post I have asked how to improve the query below which currently returns:

Now I have another question. How to modificate the code to have final cluster string only with ErrorCodes which are unique in entire string so for line 1 returns only one B, C, A (skip second C and second A).

Regards,

Arek

    DECLARE @table1 TABLE
(
    [Case] INT,
    ErrorCode CHAR(1),
    [Date] varchar(20)
);

INSERT INTO @table1
VALUES
(1, 'A', '2018-01-25'),
(1, 'B', '2018-01-15'),
(1, 'C', '2018-01-15'),
(1, 'A', '2018-01-15'),
(1, 'C', '2018-01-15'),
(1, 'A', '2018-01-15'),
(2, 'D', '2018-01-26'),
(2, 'A', '2018-01-26'),
(2, 'D', '2018-01-25'),
(2, 'C', '2018-01-24'),
(2, 'C', '2018-01-24');

SELECT *
FROM @table1;

SELECT tabel2.[Case],
       tabel2.[Date],
       STUFF(
       (
           SELECT ', ' + ErrorCode
           FROM @table1 t1
           WHERE t1.[Case] = tabel2.[Case]
                 AND t1.[Date] = tabel2.[Date]
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) AS [ErrorCode]
FROM
(SELECT DISTINCT [Case], [Date] FROM @table1) AS tabel2
ORDER BY tabel2.[Case],
         tabel2.[Date];

回答1:

If I get this correctly, it should be enough to add a DISTINCT to the sub-query returning the CSV:

   STUFF(
   (
       SELECT ** DISTINCT **  ', ' + ErrorCode --remove **
       FROM @table1 t1
       WHERE t1.[Case] = tabel2.[Case]
             AND t1.[Date] = tabel2.[Date]
       FOR XML PATH('')
   ),