sqlÓï¾ä - MS-SQL Server / »ù´¡Àà

Ò»±í
ÐÕÃû | 1 | 2 | 3 | 4 | 5 |¡£¡£¡£¡£¡£¡£
ÕÅÈý ʼ٠²¡¼Ù ʼ٠²¡¼Ù ²¡¼Ù¡£¡£¡£¡£¡£
ÀîËÄ »é¼Ù ³ö²î ³ö²î ³ö²î ³ö²î

ÈçºÎµÃ³ö
ÐÕÃû ʼ٠²¡¼Ù »é¼Þ ³ö²î¡£¡£¡£¡£
ÕÅÈý 2 3 0 0
ÀîËÄ 0 0 1 4

---

¼ÙÈçÐкÍÁеÄÄÚÈݶ¼Êǹ̶¨µÄ£¬¿ÉÒÔÓÃÒÔÏÂÓï¾ä£¬²»¹Ì¶¨ÇëʹÓö¯Ì¬SQL
SQL code:
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ÐÕÃû] varchar(4),[1] varchar(4),[2] varchar(4),[3] varchar(4),[4] varchar(4),[5] varchar(4))
insert [tb]
select 'ÕÅÈý','ʼÙ','²¡¼Ù','ʼÙ','²¡¼Ù','²¡¼Ù' union all
select 'ÀîËÄ','»é¼Ù','³ö²î','³ö²î','³ö²î','³ö²î'
go

select ÐÕÃû,
sum(case when note ='ʼÙ' then 1 else 0 end) as [ʼÙ],
sum(case when note ='²¡¼Ù' then 1 else 0 end) as [²¡¼Ù],
sum(case when note ='»é¼Þ' then 1 else 0 end) as [»é¼Þ],
sum(case when note ='³ö²î' then 1 else 0 end) as [³ö²î]
from(
select [ÐÕÃû],[1] as note from tb
union all
select [ÐÕÃû],[2] from tb
union all
select [ÐÕÃû],[3] from tb
union all
select [ÐÕÃû],[4] from tb
union all
select [ÐÕÃû],[5] from tb
) t group by ÐÕÃû
/**
ÐÕÃû ʼ٠²¡¼Ù »é¼Þ ³ö²î
---- ----------- ----------- ----------- -----------
ÀîËÄ 0 0 0 4
ÕÅÈý 2 3 0 0

(2 ÐÐÊÜÓ°Ïì)
**/

---

¶¯Ì