求助，从一个表中选取指定数据，并按行计数
表：
rid rs a12 a23 a298 a245 a234
m001 98 A A B B A
m002 c87 A A B B A
m003 u78 B B B B A
m004 u87 B B B B B
效果预计：
A出现
rid rs a12 a23 a298 a245 a234 conta
m001 98 1 1 0 0 1 3
m002 c87 1 1 0 0 1 3
m003 u78 0 0 0 0 1 1
m004 u87 0 0 0 0 0 0
[解决办法]
列的顺序变化了,应该这样
create table tb(rid varchar(10),rs varchar(10),a12 varchar(10),a23 varchar(10),
a298 varchar(10),a245 varchar(10),a234 varchar(10))
insert into tb
select 'm001','98','A','A','B','B','A'
union all select 'm002','c87','A','A','B','B','A'
union all select 'm003','u78','B','B','B','B','A'
union all select 'm004','u87','B','B','B','B','B'
declare @sql varchar(8000)
declare @s varchar(8000),@s2 varchar(8000)
set @s=''
select @s=@s+',case when ['+name+']=''A'' then 1 else 0 end as ['+name+']'
from syscolumns
where id=object_id('tb') and name not in('rid','rs')
set @s2=''
select @s2=@s2+'+['+name+']'
from syscolumns
where id=object_id('tb') and name not in('rid','rs')
set @s2=stuff(@s2,1,1,'')
set @sql='select rid,rs'+@s+ ',conta1=len('+@s2+')-len(replace('+@s2+',''A'','''')) from tb'
exec(@sql)
/*
rid rs a12 a23 a234 a245 a298 conta
------------------------------------
m00198111003
m002c87111003
m003u78001001
m004u87000000
*/
