mssql sqlserver 不固定行轉列數據(動態列)

轉自:http://www.maomao365.com/?p=5471
摘要:
下文主要講述動態行列轉換語句，列名會根據行數據的不同，
動態的發生變化
----------------------------------------------------
實現思路:
主要將待生成的動態列名，採用腳本拼接起來，然後採用pivot函數
運行，得到相應的結果
本腳本運行環境:
sql server 2008 

/*生成源數據表*/
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 
 (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腳本，生成相關數據
    
truncate table #t 
drop table #t