sql server - Group by sql query on comma joined column

Question

My table structure is like below, "Mail" column can contain multiple email joined by comma

Data(int)

Mail(varchar(200))

[Data] [Mail]

---

1 ???????? m1@gmail.com,m2@hotmail.com

2 ???????? m2@hotmail.com,m3@test.com

& I need to generate the report like below, counting each row per each email

[Mail] ????????????????? [Count]

---

m1@gmail.com ???????? 1

m2@hotmail.com ????? 2

m3@test.com ???????? ??1

So what will be the sql(server) query to generate like above? Also I can't change the table structure.

Answer 1

A SQL Server Solution

WITH T ([Data], [Mail])
     AS (SELECT 1,'m1@gmail.com,m2@hotmail.com' UNION ALL
         SELECT 2,'m2@hotmail.com,m3@test.com')
SELECT address  AS Mail,
       COUNT(*) AS [Count]
FROM   T
       CROSS APPLY (SELECT CAST('<m>' + REPLACE([Mail], ',', '</m><m>') + '</m>'
                                AS XML
                           ) AS x) ca1
       CROSS APPLY (SELECT T.split.value('.', 'varchar(200)') AS address
                    FROM   x.nodes('/m') T(split)) ca
GROUP  BY address