Post reply

Concatenate Grouped data with custom text for each value

  • April 18, 2016 at 12:58 pm

    #400038

    Following is a SQL chunk that simulates data that I am dealing with...

    Create table #TestTable (EmailID Int, EmailAddress Varchar (100))

    Insert #TestTable Values (101, 'Donald@Trump.com'), (102, 'Ted@Cruz.com'), (103, 'Hillary@Clinton.com'),

    (104, 'Bernie@Sanders.com'), (105, 'Donald@Trump.com'), (106, 'Hillary@Clinton.com'),

    (107, 'Donald@Trump.com')

    select * from #TestTable

    drop table #TestTable

    I need to get distinct EmailIDs from this table. For the most recent one (with highest ID), I have to append a text "(Currently leading)", and for all others I have to append a text "(was leading earlier"). All of these IDs along with the appended text have to be concated with "; " as the separator for each.

    My sample output for the above table would look like below...

    Donald@Trump.com (Currently leading); Hillary@Clinton.com (was leading earlier); Bernie@Sanders.com (was leading earlier), Ted@Cruz.com (was leading earlier)

    Can you please help me with a suitable solution?

    SQLCurious

  • April 18, 2016 at 1:31 pm

    #1872888

    How about something like this?

    Create table #TestTable (EmailID Int, EmailAddress Varchar (100))

    Insert #TestTable Values (101, 'Donald@Trump.com'), (102, 'Ted@Cruz.com'), (103, 'Hillary@Clinton.com'),

    (104, 'Bernie@Sanders.com'), (105, 'Donald@Trump.com'), (106, 'Hillary@Clinton.com'),

    (107, 'Donald@Trump.com');

    WITH Ordered AS (SELECT RN = ROW_NUMBER() OVER (PARTITION BY tt.EmailAddress ORDER BY tt.EmailID DESC), Email = CONCAT(EmailAddress, IIF(EmailID = (SELECT MAX(EmailID) FROM #TestTable tt), ' (Currently Leading)', ' (Was Leading Earlier)')) FROM #TestTable tt)

    SELECT Ordered.Email FROM Ordered

    WHERE Ordered.RN = 1

    drop table #TestTable

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • April 18, 2016 at 2:46 pm

    #1872904

    Depending on your indexes, the following may be better (and also produces the full concatenated string using the XML concatenation method).

    Create table #TestTable (EmailID Int, EmailAddress Varchar (100))

    Insert #TestTable Values (101, 'Donald@Trump.com'), (102, 'Ted@Cruz.com'), (103, 'Hillary@Clinton.com'),

    (104, 'Bernie@Sanders.com'), (105, 'Donald@Trump.com'), (106, 'Hillary@Clinton.com'),

    (107, 'Donald@Trump.com');

    SELECT

    STUFF

    (

    (

    SELECT '; ' + tt.EmailAddress + CASE WHEN ROW_NUMBER() OVER(ORDER BY MAX(tt.EmailID) DESC) = 1 THEN ' (Currently Leading)' ELSE ' (Was Leading Earlier)' END

    FROM #TestTable tt

    GROUP BY tt.EmailAddress

    ORDER BY MAX(tt.EmailID) DESC

    FOR XML PATH(''), TYPE

    ).value('text()[1]', 'VARCHAR(max)')

    , 1, 2, '')

    drop table #TestTable

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • April 19, 2016 at 10:22 am

    #1873120

    Thanks for the responses. I appreciate your time and effort very much!

    SQLCurious

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply