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.
See Question&Answers more detail:os