sql字符拆分字段，并统计（在线等）
有一张表如图
需要统计成如下图
如何用sqlserver试下，sql2008，求指教。
[解决办法]
--实现split功能 的函数
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
go
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([xueke] varchar(50),[hits] int)
insert [a]
select '数学，语文',2 union all
select '语文，英语',3 union all
select '化学，物理',4 union all
select '物理，生物',6 union all
select '化学',3
if object_id('[b]') is not null drop table [b]
go
create table [b]([xueke] varchar(50),[hits] int)
select row_number() over(order by xueke ) as rn,* into #a from a
declare @i int ,@j int ,@k varchar(10),@l int
set @i =1
select @j=max(rn) from #a
while(@i<=@j)
begin
select @k=[xueke],@l= [hits] from #a where rn =@i
insert into b select a,@l from dbo.f_split(@k,',')
set @i=@i+1
end
select xueke ,sum(hits) as hits from b group by xueke
create table #tb([xueke] varchar(50),[hits] int)
insert #tb
select '数学，语文',2 union all
select '语文，英语',3 union all
select '化学，物理',4 union all
select '物理，生物',6 union all
select '化学',3
select [xueke],SUM(hits) as hits
from
(SELECT SUBSTRING([xueke],number,CHARINDEX('，',[xueke]+'，',number)-number) as [xueke],a.hits
from #tb a, master..spt_values
where number >=1 and type='p'
and number<len([xueke]) and substring('，'+[xueke],number,1)='，'
)t
group by [xueke]
order by 2
/*
xuekehits
数学2
英语3
语文5
生物6
化学7
物理10
*/
;with cte as
(select SUBSTRING(xueke,0,charindex(',',xueke)) xueke,hits from T1218
union all select RIGHT(xueke,len(xueke)-charindex(',',xueke)) xueke,hits from T1218)
select xueke,SUM(hits) from cte where xueke<>'' group by xueke order by xueke desc
/* xueke hits
语文5
英语3
物理10
数学2
生物6
化学7 */
