全表行转列——动态SQL

具体代码实现如下(点击代码展开):

 

--title：全表行转列——动态SQL
--author：【DBA】小七
--create date：2009-11-21
use master
create table tb(id char(2) ,AA CHAR(10),BB char(10),CC CHAR(10))
INSERT INTO tb 
SELECT '1','ASGAHDF','ccc','HSNS' union all
select '2','sdfsa','dd','SBSH' UNION ALL
select '3','sSGDH','DCD','JRTJK' UNION ALL
select '4','fJKDH','OP9V','T48JI' UNION ALL
select '5','sJDNR','NTY','T161I' UNION ALL
select '6','DNDH','HSDR','XDYURI' UNION ALL
select '7','DNJKDH','DHN','TSDJ' UNION ALL
select '8','sJngKH','HBNRV','TYMRT9I' UNION ALL
select '9','Krm5DH','SHE','D9NKUI' UNION ALL
select '10','DH46m','SJN5R','J0OKUI'
SELECT * FROM tb

go
declare @sql1 varchar(8000),@sql2  varchar(8000),@sql3  varchar(8000),@sql4  varchar(8000)


set @sql1='select ''AA'' id '
set @sql2='select ''BB'' id'
set @sql3='select ''CC'' id'
set @sql4=''
select @sql1 = @sql1 +',  (case id when id  then '''+AA+'''  end) ['+ id+' ]' from (select distinct * from tb) as a 
set @sql1=@sql1+' from tb   '
select @sql2 = @sql2 +',  (case id when id  then '''+BB+'''  end) ['+ id+' ]' from (select distinct * from tb) as b
set @sql2=@sql2+' from tb   '
select @sql3 = @sql3 +',  (case id when id  then '''+CC+'''  end) ['+ id+' ]' from (select distinct * from tb) as a 
set @sql3=@sql3+' from tb   '
set @sql1= 'select distinct * from ('+@sql1+') as a '
set @sql2= 'select distinct * from ('+@sql2+') as b'
set @sql3= 'select distinct * from ('+@sql3+') as c'
set @sql4 = @sql1+'  union all  '+@sql2+'  union all  '+@sql3
exec(@sql4)

go
drop table tb


（所影响的行数为 10 行）

id   AA         BB         CC         
---- ---------- ---------- ---------- 
1    ASGAHDF    ccc        HSNS      
2    sdfsa      dd         SBSH      
3    sSGDH      DCD        JRTJK     
4    fJKDH      OP9V       T48JI     
5    sJDNR      NTY        T161I     
6    DNDH       HSDR       XDYURI    
7    DNJKDH     DHN        TSDJ      
8    sJngKH     HBNRV      TYMRT9I   
9    Krm5DH     SHE        D9NKUI    
10   DH46m      SJN5R      J0OKUI    

（所影响的行数为 10 行）

id   1          10         2          3          4          5          6          7          8          9          
---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
AA   ASGAHDF    DH46m      sdfsa      sSGDH      fJKDH      sJDNR      DNDH       DNJKDH     sJngKH     Krm5DH    
BB   ccc        SJN5R      dd         DCD        OP9V       NTY        HSDR       DHN        HBNRV      SHE       
CC   HSNS       J0OKUI     SBSH       JRTJK      T48JI      T161I      XDYURI     TSDJ       TYMRT9I    D9NKUI 

自定义搜索