日期 项目 金额 数量
2013-09-02 a 20 2
2013-09-06 b 1 2
2013-09-06 c 999 10
2013-09-09 d 400 4
2013-09-09 e 140 14结果
日期 a b c d e
2013-09-02 20 0 0 0 0
2013-09-02 2 0 0 0 0
2013-09-06 0 1 999 0 0
2013-09-06 0 2 10 0 0
2013-09-09 0 0 0 400 140
2013-09-09 0 0 0 4 14即将金额和数量都转,请教大侠行列转
insert into #tb
select '2013-09-02','a',20,2
union all select '2013-09-06','b',1,2
union all select '2013-09-06','c',999,10
union all select '2013-09-09','d',400,4
union all select '2013-09-09','e',140,14declare @groupField varchar(1000)
select @groupField=isnull(@groupField,'')+case when isnull(@groupField,'')='' then '' else ',' end+QUOTENAME(项目)
from (select distinct rtrim(项目) as 项目 from #tb)t
declare @sql nvarchar(4000)
set @sql=N'select *
from
(select 日期,项目,sum(金额) as 金额
from #tb
group by 日期,项目
) as x
pivot (sum(金额)for 项目 in ('+@groupField+')) as pvt'
set @sql=@sql+N' union all select *
from
(select 日期,项目,sum(数量) as 数量
from #tb
group by 日期,项目
) as x
pivot (sum(数量)for 项目 in ('+@groupField+')) as pvt'
EXEC ('select * from ('+@sql+')t order by 日期')/*
日期 a b c d e
--------------------------------------------------
2013-09-02 20 NULL NULL NULL NULL
2013-09-02 2 NULL NULL NULL NULL
2013-09-06 NULL 2 10 NULL NULL
2013-09-06 NULL 1 999 NULL NULL
2013-09-09 NULL NULL NULL 400 140
2013-09-09 NULL NULL NULL 4 14
*/
Declare @sql varchar(max)
set @sql=STUFF((select ','+QUOTENAME(CONVERT(varchar(10),[项目])) from [#tb] group by [项目] FOR XML PATH('')) ,1,1,'')set @sql='select [日期],'+@sql+' from [#tb] t
pivot (sum([金额])for [项目] in ('+@sql+')) a
union
select [日期],'+@sql+' from [#tb] t
pivot (sum([数量])for [项目] in ('+@sql+')) a'
exec(@sql)
Declare @s varchar(max)select @s=isnull(@s+',','')+'['+项目+']' from #tb group by 项目 order by 项目
set @s='select * from (select [日期],'+@s+ 'from #tb pivot (sum(金额) for 项目 in ('+@s+'))a
union all
select [日期],'+@s+ ' from #tb pivot (sum(数量) for 项目 in ('+@s+')) b) a order by [日期]'
exec(@s)