T-SQL code to group data into comma delimited column

  • sharonrao123

    Default port

    Points: 1450

    hi All,

    I have a view v_Newsletter with has two columns email and newsletter. There are some emails which has subscribed to more than one newsletter. Please see below for example data

    Email Newsletter

    Email1 Newsletter1

    Email1 Newsletter2

    Email2 Newsletter1

    Email3 Newsletter1

    Email3 Newsletter2

    Email 4 Newsletter2

    Now is it possible to create an excel file with two columns email and newsletter with email address occuring only once and newsletter as comma delimited field for multiple newsletter. Like below format

    Email Newsletter

    Email1 Newsletter1,Newsletter2

    Email2 Newsletter1

    Email3 Newsletter1, Newsletter2

    Email4 Newsletter2

    Please advice on how to proceed with the T-SQL code.

    Thanks, Shilpa.

  • Lowell

    SSC Guru

    Points: 323361

    ugg...i was pasting an example that uses the FOR XML to resolve this, and realized you posted in a SQL 2000 forum.

    the best way is to use the trick that the FOR XML command can do for you; even for large lists, is pretty efficient.

    i'm pretty sure it still works, i changed my 2005 database to compatibility level 80, and it worked just fine, but you'll need to test it:

    here's typical results with the values i used...i was too lazy to change every data element to your examples.

    Resource_Id Skills

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

    Email1 oracle,sqlserver,vb.net

    Email2 excel,java,sqlserver

    Email3 java,oracle,vb.net

    declare @skills table (Resource_Id varchar(30), Skill_Id varchar(20))

    insert into @skills

    select 'Email1' , 'sqlserver' union all

    select 'Email1' , 'vb.net' union all

    select 'Email1' , 'oracle' union all

    select 'Email2', 'sqlserver' union all

    select 'Email2', 'java' union all

    select 'Email2', 'excel' union all

    select 'Email3', 'vb.net' union all

    select 'Email3', 'java' union all

    select 'Email3', 'oracle'

    ---

    select * from @skills s1

    --- Concatenated Format

    set statistics time on;

    SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id

    FROM @skills s2

    WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below

    ORDER BY Skill_Id

    FOR XML PATH('')

    ),1,1,'') as [Skills]

    FROM @skills s1

    GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned

    ORDER BY s1.Resource_Id

    set statistics time off;

    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!

  • Garadin

    One Orange Chip

    Points: 29613

    i'm pretty sure it still works, i changed my 2005 database to compatibility level 80, and it worked just fine, but you'll need to test it:

    Alas, it does not :/. This trick is 2005+. There really weren't any particularly good ways to do it in SQL 2000.

    Here's a good post on the different ways this is normally accomplished, you can pick your poison.

    http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Garadin

    One Orange Chip

    Points: 29613

    Edit: Silly late night forum lag.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • sharonrao123

    Default port

    Points: 1450

    Thanks for your suggestion the "stuff" worked like a charm.

  • sharonrao123

    Default port

    Points: 1450

    Thanks for your suggestion the "stuff" worked like a charm. You are a star

  • Garadin

    One Orange Chip

    Points: 29613

    sharonrao123 (11/24/2009)


    Thanks for your suggestion the "stuff" worked like a charm. You are a star

    Just so that people reading this later don't get the wrong idea, can you confirm the version of SQL server you are using? (SELECT @@VERSION if you're not sure) This method does not work on SQL 2000.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • pcasey

    SSC Enthusiast

    Points: 112

    I found your post by using a search engine. It is just what I needed and easy to understand. Thank you SO MUCH.:-)

  • sagar_d_jadhav

    Grasshopper

    Points: 10

    @lowell, I checked your code and it works like a charm. But I do not understand how it does allow us to group by without using any aggregate function.
    When I try executing the below code on the sample AdventureWorksDW database in SQL Server 2014, I get an error stating that I have to use an aggregate function so as to use group by.

    select firstname,
    isnull(stuff((select (',' + e2.FirstName) from DimEmployee e2
    where e2.ParentEmployeeKey = e1.EmployeeKey
    for xml path('')),1,1,''),'') list
    from DimEmployee e1
    group by e1.FirstName

  • michael.cole 47030

    Say Hey Kid

    Points: 676

    sagar_d_jadhav - Tuesday, December 18, 2018 6:00 AM

    @lowell, I checked your code and it works like a charm. But I do not understand how it does allow us to group by without using any aggregate function.
    When I try executing the below code on the sample AdventureWorksDW database in SQL Server 2014, I get an error stating that I have to use an aggregate function so as to use group by.

    select firstname,
    isnull(stuff((select (',' + e2.FirstName) from DimEmployee e2
    where e2.ParentEmployeeKey = e1.EmployeeKey
    for xml path('')),1,1,''),'') list
    from DimEmployee e1
    group by e1.FirstName

    Get rid of the isnull and try again.

  • sagar_d_jadhav

    Grasshopper

    Points: 10

    Doesn't work after removing the ISNULL

  • Chris Wooding

    SSCarpal Tunnel

    Points: 4228

    sagar_d_jadhav - Tuesday, December 18, 2018 6:00 AM

    @lowell, I checked your code and it works like a charm. But I do not understand how it does allow us to group by without using any aggregate function.
    When I try executing the below code on the sample AdventureWorksDW database in SQL Server 2014, I get an error stating that I have to use an aggregate function so as to use group by.

    select firstname,
    isnull(stuff((select (',' + e2.FirstName) from DimEmployee e2
    where e2.ParentEmployeeKey = e1.EmployeeKey
    for xml path('')),1,1,''),'') list
    from DimEmployee e1
    group by e1.FirstName

    You could try using SELECT DISTINCT instead of GROUP BY to get rid of the duplicates.

Viewing 12 posts - 1 through 12 (of 12 total)

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