1 create table #tb 2 ( 3  id int, 4  col varchar(50), 5  num int 6 ) 7 insert into #tb select 1,'aa,bb,cc,',10 8 union all select 2,'dd,bb,aa,',20 9 union all select 3,'cc,aa,ff,',3010 11 with segmentations as12 (13    select b.id,14           number=substring(col,A.id,charindex(',',col+',',A.id)-A.id)15     from #TT A join #tb b on substring(','+col,A.id,1)=','16 )17 select number,count(distinct id) [count],count(number) [number] from segmentations group by number