数据表中某列的内容用","分隔，怎样统计所有分隔内容的个数？
如有表：
table1:
col1 col2
1 a,b,c,d,f
2 a,c,e
3 b,c,d
统计结果：
a：2
b：2
c：3
d：2
e：1
f：1
其中，col2列中用","分隔内容(a,b,c,d等)，并且在每一行中是唯一的；
请问sql语句怎么写？ SQL
[解决办法]
USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL1 INT,COL2 VARCHAR(5000))
GO
INSERT INTO TB
SELECT 1,'a,b,c,d,f' UNION ALL
SELECT 2,'a,c,e' UNION ALL
SELECT 3,'b,c,d'
GO
SELECT SUBSTRING(T1.COL2,T2.NUMBER,CHARINDEX(',',T1.COL2+',',NUMBER)-T2.NUMBER),COUNT(1)
FROM TB T1
INNER JOIN MASTER..SPT_VALUES T2
ON T2.TYPE='P' AND CHARINDEX(',',','+T1.COL2,T2.NUMBER)=T2.NUMBER
GROUP BY SUBSTRING(T1.COL2,T2.NUMBER,CHARINDEX(',',T1.COL2+',',NUMBER)-T2.NUMBER)
/*
a2
b2
c3
d2
e1
f1
*/
create table ct(id int,t1 varchar(2000))
insert into ct select 1,'a,b,c,d,f'
union all
select 2,'a,c,e'
union all
select 3,'b,c,d'
select * from ct
declare @t1 varchar(2000),@str varchar(3000)
declare @t table (sql nvarchar(4000))
declare cur_hh cursor for select t1 from ct
open cur_hh
fetch next from cur_hh into @t1
while @@fetch_status=0
begin
while len(@t1)>0
begin
set @str=substring(@t1,1,case patindex('%,%',@t1) when 0 then 200 else patindex('%,%',@t1)-1 end)
insert into @t select @str
set @t1=substring(@t1,case patindex('%,%',@t1) when 0 then 800 else patindex('%,%',@t1)+1 end,3000)
end
fetch next from cur_hh into @t1
end
close cur_hh
deallocate cur_hh
select sql,count(1) 数量 from @t group by sql
