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

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

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<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>