SQL行转列

create table #tTemp
(
  iID                int IDENTITY,
  cTechParaName      varchar(64) null,       
  cTechParaName2       varchar(64) NULL,  
  iSerial      varchar(32)              
)
insert into #tTemp(cTechParaName,cTechParaName2,iSerial)
select cTechParaName,isnull(cTechParaName2,cTechParaName) as cTechParaName2,iSerial 
 from tEqaTechParaDefine where cEquiSortCode=0001 order by iSerial
select cTechParaName,isnull(cTechParaName2,cTechParaName) as cTechParaName2,iSerial 
 from tEqaTechParaDefine where cEquiSortCode=0001 order by iSerial

create table #tColumn_Temp
(
  iID          int,    
)
insert into #tColumn_Temp(iID) values(1)
insert into #tColumn_Temp(iID) values(2)
declare @iCount int,@i int,@cTechParaName varchar(64),@cTechParaName2 varchar(64),@strSql varchar(6000),@strColumn varchar(32)
select @iCount = COUNT(1) from #tTemp
select @i=1,@cTechParaName = ‘‘ ,@cTechParaName2=‘‘, @strColumn=‘‘
while @i <= @iCount
begin
        select @cTechParaName = cTechParaName,@cTechParaName2 = cTechParaName2 from #tTemp where iID = @i
        set @strColumn = rtrim(ltrim(STR(@i)))
        exec(alter table #tColumn_Temp add [+  @strColumn +] varchar(64) null ) 
        
        
         SET @strSql = update #tColumn_Temp set [ + @strColumn + ] = ‘‘‘ + @cTechParaName  + ‘‘‘ where iID = 1
        exec(@strSql)
        
        SET @strSql = update #tColumn_Temp set [ + @strColumn+ ] = ‘‘‘ + @cTechParaName2  + ‘‘‘ where iID = 2
        exec(@strSql)
      
        set @i = @i +1
end

select * from #tColumn_Temp
drop table #tColumn_Temp
drop table #tTemp

SQL行转列

 

SQL行转列

 

SQL行转列

上一篇:TDSQL是否有全局时钟?


下一篇:SQL Server2000安装教程