一道有些难度的SQL题^^^^^^^^^^^^^^^^^^^^^^^^^

[cefriend]

if object_id('cl') is not null
drop table cl
go
create table cl(col1 nvarchar(10),col2 nvarchar(100))
go
insert into cl(col1,col2)
select '1','a,bb,c,d,bb' union all
select '2','1,2,3' union all
select '3','a,b,2'

如何在整张表中统计出字符的个数
结果是
标识 个数
a 2
bb 2
c 1
d 1
1 1
2 2
3 1

[wzy_love_sly]

if object_id('cl') is not null
drop table cl
go
create table cl(col1 nvarchar(10),col2 nvarchar(100))
go
insert into cl(col1,col2)
select '1','a,bb,c,d,bb' union all
select '2','1,2,3' union all
select '3','a,b,2'

select 
    b.Col2 as 标识,count(1) as 个数
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from cl)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
group by b.Col2

标识 个数
1 1
2 2
3 1
a 2
b 1
bb 2
c 1
d 1

[wzy_love_sly]

2000加临时表拆分后，汇总

[cefriend]

要SQL2000的,2005的不通用