摘要:
下文主要讲述动态行列转换语句，列名会根据行数据的不同，
动态的发生变化
—————————————————-
实现思路:
主要将待生成的动态列名，采用脚本拼接起来，然后采用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