sybaes ase 12.0 上一个横表转纵表的简单例子 千千如梦 2002年10月 /* create table ... */create table #dest_table( flag char(1), a int null, b int null, c int null, d int null, e int null )go/* flag 可以是一个或多个字段;但 flag 须能建立 unique index */create unique index idx_prk on #dest_table (flag)gocreate table #mid_table( flag char(1), item char(1), num int )go/* flag 可以是一个或多个字段;但 flag 与 item 能建立 unique index */create unique index idx_prk on #mid_table (flag,item)go/* init data ... */insert #mid_table values ('a','a',100)insert #mid_table values ('a','b',200)insert #mid_table values ('a','c',300)insert #mid_table values ('a','d',400)insert #mid_table values ('a','e',500)goinsert #mid_table values ('b','a',10)insert #mid_table values ('b','b',20)insert #mid_table values ('b','c',30)insert #mid_table values ('b','d',40)insert #mid_table values ('b','e',50)goinsert #mid_table values ('c','a',9)insert #mid_table values ('c','b',8)insert #mid_table values ('c','d',6)insert #mid_table values ('c','e',5)goinsert #mid_table values ('x','a',22)godeclare cur_name cursor for select flag,item,num from #mid_table for read onlygo/* cursor var ... */declare @flag char(1) declare @item char(1) declare @num int/* other var ... */declare @sql_text char(255)declare @col_char char(1)open cur_namefetch cur_name into @flag,@item,@numwhile (@@sqlstatus=0)begin /* 注意 #mid_table 与 #dest_table 字段间的对应逻辑 */ /* 此处可执行一个很复杂的转换 */ select @[email protected] if not exists (select 1 from #dest_table where [email protected] ) begin select @sql_text= "insert #dest_table (flag," [email protected]_char+") values('" [email protected]+"',"+ +convert(char(10),@num)+")" /* 调试时可将 select @sql_text 打开;exec (@sql_text) 屏蔽 */ --select @sql_text exec (@sql_text) end else begin select @sql_text= "update #dest_table set " [email protected]_char+"=" +convert(char(10),@num) +" where flag='"[email protected]+"'" --select @sql_text exec (@sql_text) end /* 偶往前游啊游~~~~~~ */ fetch cur_name into @flag,@item,@numendgoclose cur_namegodeallocate cursor cur_namegoselect * from #mid_tablegoselect * from #dest_tablego
