SYBAES ASE 12.0 上一个横表转纵表的简单例子

作者:网络 来源:佚名 更新时间:2008-02-09 01:16:53 点击:
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