sql-server - T-SQL - 使用 STUFF 连接分组列并删除重复项

Question

我有一个看起来像这样的表：

EmailAddress: nvarchar(255)
MarketingEmailOptIn: nvarchar(50)
NewsletterOptIn: nvarchar(50)
ThoughtLeaderOptIn: nvarchar(50)

下面显示的我的 SQL 语句采用上面的数据并使用逗号作为分隔符连接“订阅类型”：

SELECT  
    EmailAddress,
    STUFF((SELECT ',' + 
              CASE
                 WHEN B.MarketingEmailOptIn = 'TRUE' THEN 'MarketingEmail' 
                 WHEN B.ThoughtLeaderOptIn = 'TRUE' THEN 'ThoughtLeader'
                 WHEN B.NewsletterOptIn = 'TRUE' THEN 'Newsletter'
              END
          FROM UK_AGT_AgentForms_TEST_DE B 
          WHERE ISNULL(B.EmailAddress, '') = ISNULL(A.EmailAddress, '')
          FOR XML PATH('')), 1, 2, '') AS Subscriptions
FROM
    UK_AGT_AgentForms_TEST_DE A
GROUP BY 
    EmailAddress 

运行此 SQL 会产生以下输出：

但是请注意，MarketingEmail它列出了两次，因为源表也列出了两次（第 1 行和第 2 行）。我需要省略检测到的任何重复项，以便生成的表如下所示：

我对STUFF关键字很陌生。我只是有点迷失如何在运行时检测重复项 - 任何建议都值得赞赏。谢谢

标签： sql-servertsqlstuff

Answer 1

尝试这样的事情：

DECLARE @Data table (
    EmailAddress nvarchar(255),
    MarketingEmailOptIn nvarchar(50),
    NewsletterOptIn nvarchar(50),
    ThoughtLeaderOptIn nvarchar(50)
);

INSERT INTO @Data VALUES
    ( '[e-mail]', 'TRUE', NULL, NULL ),
    ( '[e-mail]', 'TRUE', 'TRUE', NULL ),
    ( '[e-mail]', 'TRUE', NULL, 'TRUE' );

SELECT
    EmailAddress
    , STUFF ( ( CASE WHEN EOptIn = 'TRUE' THEN ',MarketingEmail' ELSE '' END
        + CASE WHEN NOptIn = 'TRUE' THEN ',Newsletter' ELSE '' END
        + CASE WHEN TOptIn = 'TRUE' THEN ',ThoughtLeader' ELSE '' END 
    ), 1, 1, '' ) AS Subscriptions
FROM (

    SELECT TOP 100 PERCENT
        EmailAddress
        , MAX ( MarketingEmailOptIn ) AS EOptIn
        , MAX ( NewsletterOptIn ) AS NOptIn
        , MAX ( ThoughtLeaderOptIn ) AS TOptIn
    FROM @Data A --UK_AGT_AgentForms_TEST_DE
    GROUP BY EmailAddress
    ORDER BY EmailAddress

) AS x
ORDER BY 
    EmailAddress;

退货

+--------------------+-----------------------------------------+
|    EmailAddress    |              Subscriptions              |
+--------------------+-----------------------------------------+
| [e-mail] | MarketingEmail,Newsletter,ThoughtLeader |
+--------------------+-----------------------------------------+