T-SQL code to group data into comma delimited column

Question

Post reply

T-SQL code to group data into comma delimited column

sharonrao123

Default port

Points: 1450
More actions
November 18, 2009 at 3:56 pm

#73014

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
Email1Newsletter1
Email1Newsletter2
Email2Newsletter1
Email3Newsletter1
Email3Newsletter2
Email 4Newsletter2
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
EmailNewsletter
Email1Newsletter1,Newsletter2
Email2Newsletter1
Email3Newsletter1, Newsletter2
Email4Newsletter2
Please advice on how to proceed with the T-SQL code.
Thanks, Shilpa.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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

sharonrao123 Default port Points: 1450 More actions · Answer 5

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

Garadin One Orange Chip Points: 29613 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

@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 SSC Eights! Points: 804 More actions · Answer 9

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 More actions · Answer 10

sagar_d_jadhav

Grasshopper

Points: 10

January 3, 2019 at 1:54 pm

#2017920

Doesn't work after removing the ISNULL

Chris Wooding SSCarpal Tunnel Points: 4554 More actions · Answer 11

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.