SQL经典行转列

--测试表

create table tb_month
(monthid varchar(2),mongthName varchar(50))
insert into tb_month
select '01','一月'
union all select '02','二月'
union all select '03','三月'
union all select '04','四月'
union all select '05','五月'
union all select '06','六月'
union all select '07','七月'
union all select '08','八月'
union all select '09','九月'
union all select '10','十月'
union all select '11','十一月'
union all select '12','十二月'

create table tb_salelist
(SaleDt datetime,Qty numeric(10,4) )

insert into tb_salelist(SaleDt,Qty)
values ('2010-05-10',10)

 

--SQL2005行转列

DECLARE @V_col varchar(max)
set @V_col=''
SELECT @V_col=@V_col+'['+mongthName+'],' FROM tb_month
set @V_col=left(rtrim(@V_col),len(rtrim(@V_col))-1)
print @V_col

exec(
'select '+@V_col+'
from
(select tb.mongthName,Ta.Qty from tb_salelist ta,tb_month tb
where tb.monthid=month(ta.SaleDt)) as tk
pivot (sum(qty) for mongthName in ('+@V_col+')) as p')

 

--SQL2000行转列

select sum(case mongthName when '一月' then Qty else 0 end),
       sum(case mongthName when '二月' then Qty else 0 end),
       sum(case mongthName when '二月' then Qty else 0 end),
from (select tb.mongthName,Ta.Qty from tb_salelist ta,tb_month tb
where tb.monthid=month(ta.SaleDt)) as tk

declare @v_Col varchar(max),@v_sql varchar(max)
set @v_Col=''
select @v_Col=@v_Col+'sum(case mongthName when'+''''+mongthName+''''+'then Qty else 0 end) as ['+ mongthName +']'+',' from tb_month
set @v_Col=left(@v_Col,len(@v_Col)-1)
print @v_Col
set @v_sql='select '+@v_Col+' from (select tb.mongthName,Ta.Qty from tb_salelist ta,tb_month tb
where tb.monthid=month(ta.SaleDt)) as t'
exec(@v_sql)

 

--学生成绩排序
create  table tb_Student
( stid varchar(4),
  stidName varchar(50),
  sex char(1) )

insert into tb_Student(stid,stidName,sex)
select '0001','张三54','1'
union all select '0002','张的','1'
union all select '0003','张1','0'
union all select '0004','张2','0'
union all select '0005','张3','1'
union all select '0006','张4','1'

create table tb_Score
(stid varchar(4),
 score numeric(10,4) )

insert into tb_Score
select '0004',120
union all select '0001',100
union all select '0002',300
union all select '0003',250
union all select '0005',249
union all select '0006',180

select ta.stid,stidName,sex,(select count(1) from  tb_Score taa,tb_Student tbb
where taa.stid=tbb.stid  and score>=ta.score  and tbb.sex='1') as d
from tb_Score ta,tb_Student tb
where ta.stid=tb.stid and tb.sex='1'
order by d desc