Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL code to group data into comma delimited column Expand / Collapse
Author
Message
Posted Wednesday, November 18, 2009 3:56 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:23 PM
Points: 44, Visits: 187
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.
Post #821266
Posted Wednesday, November 18, 2009 8:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #821333
Posted Thursday, November 19, 2009 5:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #822023
Posted Thursday, November 19, 2009 8:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #822062
Posted Tuesday, November 24, 2009 6:12 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:23 PM
Points: 44, Visits: 187
Thanks for your suggestion the "stuff" worked like a charm.
Post #824238
Posted Tuesday, November 24, 2009 6:13 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:23 PM
Points: 44, Visits: 187
Thanks for your suggestion the "stuff" worked like a charm. You are a star
Post #824239
Posted Tuesday, November 24, 2009 7:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #824260
Posted Friday, October 4, 2013 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:00 AM
Points: 20, Visits: 113
I found your post by using a search engine. It is just what I needed and easy to understand. Thank you SO MUCH.
Post #1501727
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse