Creating a delimited string

  • Hi All, I have a need to create a delimited string so that I can use this to create a data driven subscription on SSRS, I am pretty sure I am making a fairly simple task a lot harder than it needs to be here.

    In the below code, I need to create a delimited string using the branch number, grouped by the email address

    USE tempdb

    GO

    IF OBJECT_ID('tempdb..#emails') IS NOT NULL

    BEGIN

    DROP TABLE #emails

    END

    CREATE TABLE #emails

    (

    Email VARCHAR(50) ,

    BranchNumber VARCHAR(5)

    )

    INSERT INTO #emails

    VALUES

    ( 'Branch101@ACA.com', 'A54' ),

    ( 'Branch101@ACA.com', 'A56' ),

    ( 'Branch101@ACA.com', 'A59' ),

    ( 'Branch102@ACA.com', 'B49' ),

    ( 'Branch102@ACA.com', 'B54' ),

    ( 'Branch102@ACA.com', 'B10' ),

    ( 'Branch103@ACA.com', 'C80' )

    The output I am looking for is something along the lines of...

    Email, BranchNumber

    ----------------------------------------------------------

    Branch101@ACA.com A54,A56,A59

    Branch102@ACA.com B49,B54,B10

    Branch103@ACA.com C80

    Thanks

    Jim

  • great job with the same data!

    here's one way to do it, featuring FOR XML to do the concatenation:

    /*--Results

    Email(No column name)

    Branch101@ACA.comA54, A56, A59

    Branch102@ACA.comB49, B54, B10

    Branch103@ACA.comC80

    */

    SELECT Email

    ,STUFF(

    (

    SELECT ', ' + B.BranchNumber

    FROM #emails B

    WHERE A.Email = B.Email

    FOR XML PATH(''))

    ,

    1, 2, '')

    FROM #emails A

    GROUP BY Email

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Using the technique outlined in Creating a comma-separated list (SQL Spackle)[/url] By Wayne Sheffield you could do this:

    WITH de AS

    (

    SELECT DISTINCT Email

    FROM #emails

    )

    SELECT

    Email,

    BranchNumber = STUFF((

    SELECT ',' + BranchNumber

    FROM #emails e

    WHERE e.email = de.Email

    FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

    FROM de;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan, Lowell

    Thank you both for your answers, they both work and give me exactly what I am looking for.

    Appreciate the help.

    Jim

  • Jim-S (4/7/2015)


    Alan, Lowell

    Thank you both for your answers, they both work and give me exactly what I am looking for.

    Appreciate the help.

    Jim

    Are you aware of what the difference in the two queries is intended to do?


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Hi Dwain,

    No, I'm not aware. I assumed it was pretty much two slightly different methods to achieve the same result.

  • Jim-S (4/8/2015)


    Hi Dwain,

    No, I'm not aware. I assumed it was pretty much two slightly different methods to achieve the same result.

    Alan has given you the more general form that will always work but is also slightly slower. There are some special characters that, when appearing in the string will cause the concatenated result to be off. These are found here: http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references in the section on Predefined entities in XML.

    Try this to see the difference in results:

    CREATE TABLE #emails

    (

    Email VARCHAR(50) ,

    BranchNumber VARCHAR(5)

    )

    INSERT INTO #emails

    VALUES

    ( 'Branch101@ACA.com', 'A54' ),

    ( 'Branch101@ACA.com', 'A56' ),

    ( 'Branch101@ACA.com', 'A>9' ),

    ( 'Branch102@ACA.com', 'B<9' ),

    ( 'Branch102@ACA.com', 'B''4' ),

    ( 'Branch102@ACA.com', 'B&0' ),

    ( 'Branch103@ACA.com', 'C"0' );

    -- Lowell's solution

    SELECT Email

    ,STUFF(

    (

    SELECT ', ' + B.BranchNumber

    FROM #emails B

    WHERE A.Email = B.Email

    FOR XML PATH(''))

    ,

    1, 2, '')

    FROM #emails A

    GROUP BY Email;

    -- Alan.B's solution

    WITH de AS

    (

    SELECT DISTINCT Email

    FROM #emails

    )

    SELECT

    Email,

    BranchNumber = STUFF((

    SELECT ',' + BranchNumber

    FROM #emails e

    WHERE e.email = de.Email

    FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

    FROM de;

    GO

    DROP TABLE #emails;

    Using TYPE/.value as Alan has done eliminates this parsing issue.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (4/8/2015)


    Jim-S (4/8/2015)


    Hi Dwain,

    No, I'm not aware. I assumed it was pretty much two slightly different methods to achieve the same result.

    Alan has given you the more general form that will always work but is also slightly slower. There are some special characters that, when appearing in the string will cause the concatenated result to be off. These are found here: http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references in the section on Predefined entities in XML.

    Using TYPE/.value as Alan has done eliminates this parsing issue.

    Yes I can see the difference in the results, thanks for taking the time to explain. The branch number I am using will always be 2 letters followed by a number.

    Thanks again.

    Jim

Viewing 8 posts - 1 through 7 (of 7 total)

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