分拆字符串求和

view plaincopy to clipboardprint?
--分拆字符串求和  
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) desc  
 
set rowcount @len  
select id=identity(int,1,1) into # from dbo.syscolumns A,dbo.syscolumns B  
set rowcount 0  
 
;with hgo as 
(  
   select distinct data=substring(A.col,b.id,charindex(',',A.col+',',b.id)-b.id),  
   A.num,b.id  
   from #tb A,# b   
   where substring(','+A.col,b.id,1)=',' 
)   
select data,sum(num) from hgo group by data 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/02/08/3869041.aspx