作者：未知 来源：月光软件站 加入时间：2005-2-28　月光软件站

一个交叉表.用字段明做为值:原表数据为:
 字段1    字段2    字段3
  A1            B1            C1
  A2            B2            C2
变换后:
COL1          COL2         COL3
字段1            A1                A2
字段2            B1                B2
字段3            C1                C2

create table tablename (字段1 varchar(100),字段2 varchar(100),字段3 varchar(100))
insert tablename select 'gsm900/1800mhz/gprs',  '85*44*21mm', '80'      
union all select 'gsm900/1800mhz/gprs' ,'82*46*21.5mm', '79'     
go

SELECT   top 0
 字段名=a.name 
into abc
FROM syscolumns a 
             inner join sysobjects d on a.id=d.id 
 left join sysproperties f on d.id=f.id and f.smallid=0 
where    d.xtype='U' and  d.name = 'tablename' 
order by a.id,a.colorder 

SELECT   top 0
  note =a.name 
into abcd
FROM syscolumns a 
             inner join sysobjects d on a.id=d.id 
 left join sysproperties f on d.id=f.id and f.smallid=0 
where    d.xtype='U'  --表示用户类型
            and  d.name = 'tablename'   -- 需要获取字段的表
order by a.id,a.colorder 

declare @num int , @Col varchar(50) , @Name varchar(200),  @Name1  varchar(200) , @count int , @num1 varchar(5)
select @num = 1

select @count = count(*) from tablename

while  (@count > 0)
begin
    select @Col = 'Col' + convert(varchar, @num)
    exec('ALTER TABLE  abc add [' + @Col + ']  varchar(200)  NULL ')    
    select @num = @num +1
    set  @count = @count - 1
end

  DECLARE AddNameColumns_Cursor INSENSITIVE CURSOR                    --取字段值                                        
    FOR
 SELECT  
  filedname = a.name 
 FROM syscolumns a 
              inner join sysobjects d on a.id=d.id 
  left join sysproperties f on d.id=f.id and f.smallid=0 
 where    d.xtype='U' and  d.name = 'tablename' 
 order by a.id,a.colorder 
                                                    
    OPEN AddNameColumns_Cursor                                                           
    BEGIN                                                           
      FETCH NEXT FROM AddNameColumns_Cursor INTO @Name  --对应相应字段值                                                
      WHILE @@FETCH_STATUS = 0                                                           
      BEGIN                
                                          
 insert into abc (字段名)
                      select fname = @Name

   delete abcd
      exec (' insert into abcd (note) select name = [' + @Name +']  from tablename ')
      select @num = 1
                DECLARE AddNameColumns_Cursor1 INSENSITIVE CURSOR                                                           
  FOR select  note   from abcd
  OPEN AddNameColumns_Cursor1                                                           
     BEGIN                                                           
       FETCH NEXT FROM AddNameColumns_Cursor1  INTO @Name1
       WHILE @@FETCH_STATUS = 0                                                           
       BEGIN                

            select @num1 =  convert(varchar, @num)
         
            exec('update abc  set [Col'+ @num1 + '] = ''' + @Name1+'''   where  字段名='''+  @Name +'''')

  print  'update abc  set [' + @Col + '] = ''' + @Name1+'''   where  字段名='''+  @Name +''''

                              select @num = @num +1

  FETCH NEXT FROM AddNameColumns_Cursor1 INTO @Name1
       END                                       
     END                                                           
     CLOSE AddNameColumns_Cursor1                                                           
     DEALLOCATE AddNameColumns_Cursor1     

        FETCH NEXT FROM AddNameColumns_Cursor INTO @Name                                                           
      END                                       
    END                                                           
    CLOSE AddNameColumns_Cursor                                                           
    DEALLOCATE AddNameColumns_Cursor     

select * from abc
SELECT * FROM tablename

drop table tablename
drop table abc
drop table abcd

相关软件：