SQl语名的学习

1、普通的行转列示例

 

 

静态SQL
select  name 姓名,
  max(case subject when'语文'then result else 0 end) 语文,
  max(case subject when'数学'then result else 0 end) 数学 
   from tb_subject 
    group by  name

动态SQL

指subject不止语文、数学课程。

 declare@sqlvarchar(8000800080008000)

 set@sql='select Name as '+'姓名' select@sql=@sql+' , max(case Subject when '''+ Subject +''' then Result else 0 end)

['+ Subject +']' from (selectdistinct Subject fromtb) as a set@sql=@sql+' from tb group by name' exec(@sql)

 

2、group by的SQL语句

create table #tmp(rq varchar(10),shengfu nchar(1))
 insert into #tmp values('2005-05-09','胜');
 insert into #tmp values('2005-05-09','胜');
 insert into #tmp values('2005-05-09','负');
 insert into #tmp values('2005-05-09','负');
 insert into #tmp values('2005-05-10','胜');
insert into #tmp values('2005-05-10','负');
insert into #tmp values('2005-05-10','负')
 
 
 select  rq,sum(case when shengfu='胜' then 1 else 0 end) '胜',
              sum(case when shengfu='负' then 1 else 0 end) '负'
              from #tmp group by rq