一个交叉表

一个交叉表.用字段明做为值:原表数据为:
 字段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

原文转自：http://www.ltesting.net