求一句sql语句

--借用鸟儿的数据用一下

create table a(gh  varchar(20),rq1 varchar(20),rq2 varchar(20),rq3 varchar(20),rq4 varchar(20),rq5  varchar(20)) 

insert a select  '001','A','C','D','B','C'   

union all select '002','D','A',  'C','A',  'B'   

union all select '003','C','B',  'D','A', 'D'  







declare @s nvarchar(4000)

set @s=''

select @s=isnull(@s+' union all ','')+' select gh,'+quotename(name)+ ' as name from a'

from syscolumns where id=object_id('a') and name<>'gh'



set @s=stuff(@s,1,12,'')

exec('select gh,sum(case when name =''a'' then 1 else 0 end ) a,

	sum(case when name =''b'' then 1 else 0 end ) b,

	sum(case when name =''c'' then 1 else 0 end ) c,

	sum(case when name =''d'' then 1 else 0 end ) d from ('+@s+') a group by gh')

create table # (gh varchar(3),rq1 varchar(2),rq2 varchar(2),rq3 varchar(2),rq4 varchar(2),rq5 varchar(2))

insert into # select '001','a','c','d','b','c'

insert into # select '002','d','a','c','a','b'

insert into # select '003','c','b','d','a','d'





select 

	gh,sum(case when rq1='a' then 1 else 0 end)as 'a',

	sum(case when rq1='b' then 1 else 0 end)as 'b',

	sum(case when rq1='c' then 1 else 0 end)as 'c',

	sum(case when rq1='d' then 1 else 0 end)as 'd'

from

( 

select gh,rq1 from #

union all

select gh,rq2 from #

union all

select gh,rq3 from #

union all

select gh,rq4 from #

union all

select gh,rq5 from #

)a

group by gh



/*

gh   a           b           c           d           

---- ----------- ----------- ----------- ----------- 

001  1           1           2           1

002  2           1           1           1

003  1           1           1           2



（所影响的行数为 3 行）

*/

create table a(gh  varchar(20),rq1 varchar(20),rq2 varchar(20),rq3 varchar(20),rq4 varchar(20),rq5  varchar(20)) 

insert a select  '001','A','C','D','B','C'   

union all select '002','D','A',  'C','A',  'B'   

union all select '003','C','B',  'D','A', 'D'   



declare @sql  varchar(8000)

declare @a table(x varchar(20))



select @sql='select gh'

insert @a 

select  rq1 from a 

union all select rq2 from a union all select rq3 from a union all select rq4 from a union all select rq5 from a

select @sql=@sql+',sum(case when rq1='''+x+''' then 1 else 0 end +

case when rq2='''+x+''' then 1 else 0 end +

case when rq3='''+x+''' then 1 else 0 end +

case when rq4='''+x+''' then 1 else 0 end +

case when rq5='''+x+''' then 1 else 0 end 

) ['+x+']'  from (select distinct * from @a)aa



select @sql=@sql+' from a group by gh'

exec(@sql)

--result

/*gh                   A           B           C           D           

-------------------- ----------- ----------- ----------- ----------- 

001                  1           1           2           1

002                  2           1           1           1

003                  1           1           1           2

*/

create table # (gh varchar(3),rq1 varchar(2),rq2 varchar(2),rq3 varchar(2),rq4 varchar(2),rq5 varchar(2))

insert into # select '001','a','c','d','b','c'

insert into # select '002','d','a','c','a','b'

insert into # select '003','c','b','d','a','d'





select gh,

sum(case when rq1='a' then 1 else 0 end+case when rq2='a' then 1 else 0 end+case  when rq3='a' then 1 else 0 end + case when rq4='a' then 1 else 0 end+ case when rq5='a' then 1 else 0 end) as 'a',

sum(case when rq1='b' then 1 else 0 end+case when rq2='b' then 1 else 0 end+case  when rq3='b' then 1 else 0 end + case when rq4='b' then 1 else 0 end+ case when rq5='b' then 1 else 0 end) as 'b',

sum(case when rq1='c' then 1 else 0 end+case when rq2='c' then 1 else 0 end+case  when rq3='c' then 1 else 0 end + case when rq4='c' then 1 else 0 end+ case when rq5='c' then 1 else 0 end) as 'c',

sum(case when rq1='d' then 1 else 0 end+case when rq2='d' then 1 else 0 end+case  when rq3='d' then 1 else 0 end + case when rq4='d' then 1 else 0 end+ case when rq5='d' then 1 else 0 end) as 'd'

from # group by gh