（sql）这种表结构如何查询

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2015-11-26 17:48:53
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
--	Feb 10 2012 19:39:15 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(8))
insert [tb]
select 'aa' union all
select 'bb' union all
select 'aa,bb' union all
select 'bb,cc' union all
select 'bb,cc,dd'
--------------开始查询--------------------------
SELECT col,COUNT(1) AS num 
FROM 
(
Select
    COl=substring(a.Col,b.number,charindex(',',a.Col+',',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col)
where
     substring(','+a.COl,b.number,1)=',') AS t 
GROUP BY col 

----------------结果----------------------------
/* col      num
-------- -----------
aa       2
bb       4
cc       2
dd       1

(4 行受影响)

*/