sql server 行转列

 

/*生成源数据表*/
create table #t
(compname varchar(20),
cheXi varchar(30),
dayInfo int,
daySaleValue int)

/*生成源数据*/
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','锐志','1',20)
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','皇冠','1',10)
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','霸道','2',30)
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','锐志','3',40)
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','RAV4','4',60)
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','锐志','5',8)
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','霸道','6',6)
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','RAV4','5',9)
insert into #t(compname,cheXi,dayInfo,daySaleValue) values('一汽丰田','RAV4','10',10)

select * from #t

 

/*
 select * from
 (select compname,daySaleValue,dayInfo,chexi from  #t) as d
 /*注意事项: pivot所涉及的聚合列 value_column  和 pivot_column
  都必须存在 上面的查询表中
 */
 pivot(sum(daySaleValue) for dayInfo
   in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10]))
   t ;
*/
/*拼接字符串*/
declare @sql varchar(max)
set @sql =' select * from
 (select compname,daySaleValue,dayInfo,chexi from  #t) as d
   pivot(sum(daySaleValue) for dayInfo
   in(
 ';

/*动态组合列名*/
 declare @lieMing varchar(7000)   ---定义动态生成列名存放变量
 declare @i int ,@imax int,@field varchar(60)  ---定义临时循环变量
 declare @fieldList table(keyId int identity,field varchar(60)) ---定义临时表，存放待生成动态列名的数据
 insert into @fieldList(field) select distinct dayInfo from #t  ---生成列名数据
 
 -------------循环表生成列名start--------------
 set @lieMing =''
 set @i=1
 select @imax =max(keyId) from @fieldList t
 while @i <@imax
 begin
    select @field =field from @fieldList t where t.keyId=@i
    if isnull(@field,'') !=''
     begin
        if @lieMing !='' begin set @lieMing =@lieMing +',' end
        set @lieMing = @lieMing+'['+@field+']';
     end
    set @i=@i+1
 end
  -------------循环表生成列名end--------------
/*动态组合列*/

set @sql =@sql +@lieMing +' ))  t ;';    ---拼接sql语句
exec (@sql)                              ---执行sql脚本，生成相关数据
    
--select * from #t
truncate table #t
drop table #t