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'
uni