求一句sql语句

[kylin81]

我有一个表,结构如下
gh rq1 rq2 rq3 rq4 rq5
001 A C D B C
002 D A C A B
003 C B D A D


现求一句SQL语句实现如下结果集
gh A B C D
001 1 1 2 1
002 2 1 1 1
003 1 1 1 2

这个语句怎么请啊?

[wzy_love_sly]

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

001 1 1 2 1
002 2 1 1 1
003 1 1 1 2

[dawugui]

怎么个算法?

[chuifengde]

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
*/

[fa_ge]

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 行）
*/

[fa_ge]

鸟儿是用动态写的

[火星求索]

应该有人啊，刷

[火星求索]

看见发哥了，总是看不到回的帖

[wuxinyuzhu]

--借用鸟儿的数据用一下
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')