Irritative sql statement help needed

Question

I have a table that has a column with comma delimited email addresses. Is there a way in a single select statement to parse them out and do a group by on them?

In a table like this:

ID     Emails
1      email@domain.com, email2@domamin.com
2      email2@domain.com, email3@domain.com

Return something like:

Email                Count
email@domain         1
email2@domain.com    2
email3@domain.com    1

i know i can do this with cursors and temp tables, but i wasn't sure if there was some neat trick to doing this with a select and nested select statements.

Thanks in advance.

Normalise your structure into a table `Id, Email` with one email address per row and problem solved. — Martin Smith, Dec 19 '11 at 16:37

score 2 · Accepted Answer · answered Dec 19 '11 at 16:36

You can convert your email list to XML and then query the XML using .nodes and .value.

declare @T table
(
  ID int,
  Emails varchar(100)
)

insert into @T values
(1, 'email@domain.com, email2@domain.com'),
(2, 'email2@domain.com, email3@domain.com')

select T.Email, count(*) as [Count]
from (
      select X.N.value('.',  'varchar(30)') as Email
      from @T
        cross apply (select cast('<x>'+replace(Emails, ', ', '</x><x>')+'</x>' as xml)) as T(X)
        cross apply T.X.nodes('/x') as X(N)
     ) as T
group by T.Email

Result:

Email                          Count
------------------------------ -----------
email@domain.com               1
email2@domain.com              2
email3@domain.com              1

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

Your problem seems very similar to the one posed in this question: Turning a Comma Separated string into individual rows.

A recursive CTE, as was done there, should work.

edited May 23 '17 at 11:48

Community

1
1

answered Dec 19 '11 at 16:38

mwigdahl

16,268
7
50
64

score 0 · Answer 3 · answered Dec 19 '11 at 17:55

I'll have to sit and digest this one a little bit, because never played with MSSQL xml stuff before, but i changed it to use my actual table name and it works PERFECTLY, omg, thank you so much. Here's what i did (in case someone wants to use this without a temp table like me):

select T.Email, count(*) as [Count] 
from ( 
      select X.N.value('.',  'varchar(30)') as Email 
      from reports_schedule rs
        cross apply (select cast('<x>'+replace(rs.recipient_email, ', ', '</x><x>')+'</x>' as xml)) as T(X) 
        cross apply T.X.nodes('/x') as X(N) 
     ) as T 
group by T.Email

thanks again!

Irritative sql statement help needed

3 Answers3

Linked