select id,aaa=sum(case when charindex('111',result) >0 then 1 else 0 end)
bbb=sum(case when charindex('222',result) >0 then 1 else 0 end)
ccc=sum(case when charindex('333',result) >0 then 1 else 0 end)
ddd=sum(case when charindex('444',result) >0 then 1 else 0 end)
eee=sum(case when charindex('555',result) >0 then 1 else 0 end)
from tb2
group by id
bbb=sum(case when charindex('222',result) >0 then 1 else 0 end)
ccc=sum(case when charindex('333',result) >0 then 1 else 0 end)
ddd=sum(case when charindex('444',result) >0 then 1 else 0 end)
eee=sum(case when charindex('555',result) >0 then 1 else 0 end)
from tb2
group by id
insert into @tb2 select
1 , '111,222,' union all select
2 , '222,333,' union all select
3 , '111,333,'--测试
select id,aaa=sum(case when charindex('111',result) >0 then 1 else 0 end),
bbb=sum(case when charindex('222',result) >0 then 1 else 0 end),
ccc=sum(case when charindex('333',result) >0 then 1 else 0 end),
ddd=sum(case when charindex('444',result) >0 then 1 else 0 end),
eee=sum(case when charindex('555',result) >0 then 1 else 0 end)
from @tb2
group by id--结果
id aaa bbb ccc ddd eee
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 0 0 0
2 0 1 1 0 0
3 1 0 1 0 0(3 row(s) affected)
create table tb1(id int, idname varchar(30))
insert into tb1 select
111 , 'aaa' union all select
222 , 'bbb' union all select
333 , 'ccc' union all select
444 , 'ddd' union all select
555 , 'eee'
select * from tb1create table tb2 (id int, result varchar(30))
insert into tb2 select
1 , '111,222,' union all select
2 , '222,333,' union all select
3 , '111,333,'--测试
declare @str varchar(4000)
set @str=''
select @str=@str+','+idname+'=sum(case when charindex('''+cast(id as varchar)+''',result)>0 then 1 else 0 end)' from tb1
select @strexec('select id'+@str+'from tb2 group by id')
--结果
id aaa bbb ccc ddd eee
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 0 0 0
2 0 1 1 0 0
3 1 0 1 0 0(3 row(s) affected)