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

GO

WITH T ([DATA], [Mail])
     AS (SELECT 1,'m1@gmail.com,m2@hotmail.com' UNION ALL
         SELECT 2,'m2@hotmail.com,m3@test.com')
SELECT *
FROM   T


GO

WITH T ([DATA], [Mail])
     AS (SELECT 1,'m1@gmail.com,m2@hotmail.com' UNION ALL
         SELECT 2,'m2@hotmail.com,m3@test.com')
SELECT *
FROM   T
       CROSS APPLY (SELECT CAST('<m>' + REPLACE([Mail], ',', '</m><m>') + '</m>'
                                AS XML
                           ) AS x) ca1