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