create table #tb   
(   
 id int,   
 col varchar(50),   
 num int  
)   
insert into #tb select 1,'aa,bb,cc',10   
union all select 2,'aa,aa,bb',20   
union all select 3,'aa,aa,bb',20   
union all select 4,'dd,ccc,c',30   
union all select 5,'ddaa,ccc',40   
union all select 6,'eee,ee,c',50   
  
declare @Len int  
select top 1 @Len=len(col)+1 from #tb order by len(col)   
select @Len   
set rowcount @Len   
select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B   
set rowcount 0   
;with hgo as  
(   
   select b.id,   
          number=substring(col,A.id,charindex(',',col+',',A.id)-A.id)   
    from #TT A join #tb b on substring(','+col,A.id,1)=','  
)   
select number,count(distinct id) [count],count(number) [number] from hgo group by number 

create table #tb
(
 id int,
 col varchar(50),
 num int
)
insert into #tb select 1,'aa,bb,cc',10
union all select 2,'aa,aa,bb',20
union all select 3,'aa,aa,bb',20
union all select 4,'dd,ccc,c',30
union all select 5,'ddaa,ccc',40
union all select 6,'eee,ee,c',50

--SQL查询如下：

;WITH Numbers AS
(
    SELECT TOP(50)
        Seq=ROW_NUMBER() OVER(ORDER BY [object_id])
    FROM sys.objects
),
Liang AS
(
    SELECT
        A.Seq,
        B.id,
        SUBSTRING(B.col,A.Seq,CHARINDEX(',',B.col+',',A.Seq)-A.Seq) AS v
    FROM Numbers AS A
        JOIN #tb AS B
            ON SUBSTRING(','+B.col,A.Seq,1)=','
)
SELECT 
    v AS data,
    COUNT(DISTINCT id) AS [count],
    COUNT(*) AS numbers
FROM Liang
GROUP BY v

DROP TABLE #tb