--字符串拆分

create table #tab
(
    val nvarchar(50)
)


declare @a nvarchar(1000)
declare @len int

select @a='A,B,C,A,C,D'
select @len=charindex(',',@a)  

while(@len>0)
begin
    insert into #tab(val) values(substring(@a,1,@len-1))
    
    select @a=substring(@a,@len+1,len(@a)-@len)
    
    select @len=charindex(',',@a)

end
insert into #tab(val) values(@a)


select * from #tab


drop table #tab

------解决方案--------------------
SQL code

/*按照符号分割字符串*/
create function [dbo].[m_split2](@c varchar(2000),@split varchar(2))   
  returns @t table(col varchar(200))   
  as   
    begin   
      while(charindex(@split,@c)<>0)   
        begin   
            if(substring(@c,1,charindex(@split,@c)-1)!=' ')
            begin
          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))   
            end
             set @c = stuff(@c,1,charindex(@split,@c),'')   
        end   
        if(@c!=' ' and @c is not null and @c!='')
        begin
      insert @t(col) values (@c) 
      end  
      return   
end

declare @d1 varchar(10)
set @d1='A,B,C,A,C,D'
declare @d2 varchar(10)
set @d2='1,2,5,4,3,1'

select a.col,sum(b.id) as id from 
(select *,row_number() over (order by (select 1)) as id from [dbo].[m_split2](@d1,',')) a
left join (
select *,row_number() over (order by (select 1)) as id from [dbo].[m_split2](@d2,',')) b
on a.id=b.id group by a.col
/*
col         id
----------- --------------------
A           5
B           2
C           8
*/