一個交叉表

一個交叉表.用欄位明做為值:原表資料為:
 欄位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