日期:2014-05-17 浏览次数:20586 次
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'