SYBAES ASE 12.0 上一个横表转纵表的简单例子-数据库专栏,SQL Server

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 @col_char=@item      if not exists (select 1                   from #dest_table                  where flag=@flag                 )      begin         select @sql_text= “insert #dest_table (flag,”                          +@col_char+”) values(”                          +@flag+”,”+                          +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 ”                          +@col_char+”=”                          +convert(char(10),@num)                          +” where flag=”+@flag+””         –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