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

SYBASE 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)
go

create 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)
go

insert #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)
go

insert #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)
go

insert #mid_table values ('x','A',22)
go

declare cur_name cursor for
select Flag,Item,Num from #mid_table
for read only
go

/* 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_name
fetch cur_name into @flag,@item,@num

while (@@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,@num
end
go

close cur_name
go

deallocate cursor cur_name
go

select * from #mid_table
go

select * from #dest_table
go