mssql sqlserver 不固定行转列数据(动态列)
标签：-- isnull info www mssql 实现 target hex UNC
mssql sqlserver 不固定行转列数据(动态列)
生成源数据表*/
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