--测试表
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
1. 定义游标定义
游标语句的核心是定义了一个游标标识名，并把游标标识名和一个查询语句关联起来。DECLARE语句用于声明游标，它通过SELECT查询定义游标存储的数据集合。语句格式为：
DECLARE 游标名称 [INSENSITIVE] [SCROLL]
CURSOR FOR select语句
[FOR{READ ONLY|UPDATE[OF 列名字表]}]
参数说明：
INSENSITIVE选� ......
