Saya memiliki tabel yang terlihat seperti ini: EmailAddress: nvarchar(255) MarketingEmailOptIn: nvarchar(50) NewsletterOptIn: nvarchar(50) ThoughtLeaderOptIn: nvarchar(50) Pernyataan SQL saya yang ditunjukkan di bawah ini membutuhkan ...

-1
Mike Marks 5 April 2021, 20:43

3 jawaban

Jawaban Terbaik

Pew. Saya harus bermain-main dengan yang satu ini. Mungkin bukan solusi sempurna, tapi saya pikir saya bisa mencapai apa yang Anda coba. Itu tidak menggunakan fungsi barang. Itu hanya menggabungkan setiap string dan kemudian menghapus koma terakhir.

SELECT EmailAddress, CASE WHEN LEN(Subscriptions) > 0 THEN LEFT(Subscriptions, LEN(Subscriptions) - 1) ELSE '' END AS Subscriptions
FROM (
    SELECT EmailAddress, CONCAT(
            CASE WHEN SUM(CASE WHEN MarketingEmailOptIn = 'TRUE' THEN 1 ELSE 0 END) > 0 THEN 'MarketingEmail, ' ELSE '' END,
            CASE WHEN SUM(CASE WHEN NewsletterOptIn = 'TRUE' THEN 1 ELSE 0 END) > 0 THEN 'Newsletter, ' ELSE '' END,
            CASE WHEN SUM(CASE WHEN ThoughtLeaderOptIn = 'TRUE' THEN 1 ELSE 0 END) > 0 THEN 'ThoughLeader, ' ELSE '' END
        ) AS Subscriptions
    FROM UK_AGT_AgentForms_TEST_DE 
    GROUP BY EmailAddress
) AS a
1
kaladin_storm 5 April 2021, 18:37

Cobalah sesuatu seperti ini:

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

INSERT INTO @Data VALUES
    ( 'mike@mikemarks.com', 'TRUE', NULL, NULL ),
    ( 'mike@mikemarks.com', 'TRUE', 'TRUE', NULL ),
    ( 'mike@mikemarks.com', '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;

Kembali

+--------------------+-----------------------------------------+
|    EmailAddress    |              Subscriptions              |
+--------------------+-----------------------------------------+
| mike@mikemarks.com | MarketingEmail,Newsletter,ThoughtLeader |
+--------------------+-----------------------------------------+
1
Critical Error 5 April 2021, 18:47

Jika Anda memiliki Sql Server 2017 atau yang lebih baru, Anda dapat menggunakan String_agg() untuk menyederhanakan ini:

SELECT   
    EmailAddress,
        STRING_AGG(CASE
                 WHEN MarketingEmailOptIn = 'TRUE' THEN 'MarketingEmail' 
                 WHEN ThoughtLeaderOptIn = 'TRUE' THEN 'ThoughtLeader'
                 WHEN NewsletterOptIn = 'TRUE' THEN 'Newsletter'
              END, ', ') AS Subscriptions
FROM
    UK_AGT_AgentForms_TEST_DE
GROUP BY 
    EmailAddress

Jika Anda masih melihat duplikat, Anda dapat menggunakan agregasi bersyarat dalam kueri bertingkat untuk menggulungnya terlebih dahulu:

SELECT  
    EmailAddress,
          CASE WHEN MarketingEmailOptIn > 0 THEN 'MarketingEmail,' ELSE '' END
        + CASE WHEN ThoughtLeaderOptIn > 0 THEN 'ThoughtLeader,' ELSE '' END
        + CASE WHEN NewsletterOptIn = > 0 THEN 'Newsletter' ELSE '' END
         AS Subscriptions
FROM (
    SELECT EmailAddress
        , SUM(CASE WHEN MarketingEmailOptIn = 'TRUE' THEN 1 ELSE 0 END) MarketingEmailOptIn
        , SUM(CASE WHEN ThoughtLeaderOptIn = 'TRUE' THEN 1 ELSE 0 END) ThoughtLeaderOptIn
        , SUM(CASE WHEN NewsletterOptIn = 'TRUE' THEN 1 ELSE 0 END) NewsletterOptIn
    FROM UK_AGT_AgentForms_TEST_DE
    GROUP BY EmailAddress
) T
1
Joel Coehoorn 5 April 2021, 18:45