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