将原始数据转换为列中的字符串 - 仅当唯一时 [英] Raws into Strings in Columns - only if unique

问题描述

在另一篇文章中，我询问了如何改进当前返回的查询:

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

现在我还有一个问题.如何修改代码以仅使用在整个字符串中唯一的 ErrorCodes 来获得最终集群字符串，以便第 1 行仅返回一个 B、C、A(跳过第二个 C 和第二个 A).

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).

问候，

阿瑞克

    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];

推荐答案

如果我理解正确，那么在返回 CSV 的子查询中添加一个 DISTINCT 就足够了:

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('')
   ),

这篇关于将原始数据转换为列中的字符串 - 仅当唯一时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！