--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (商品名称 varchar(1),季度 int,销售额 int)
insert into #T
select 'a',1,1999 union all
select 'a',2,3333 union all
select 'a',4,3434 union all
select 'b',1,3345 union all
select 'b',2,3434 union all
select 'b',3,4564--> 2000 静态
select
商品名称,
第1季度销售额=sum(case 季度 when 1 then 销售额 else 0 end),
第2季度销售额=sum(case 季度 when 2 then 销售额 else 0 end),
第3季度销售额=sum(case 季度 when 3 then 销售额 else 0 end),
第4季度销售额=sum(case 季度 when 4 then 销售额 else 0 end)
from #T
group by 商品名称--> 2000 动态
declare @SQL varchar(8000)
select @SQL=isnull(@SQL,'商品名称')+',第'+ltrim(季度)+'季度销售额=sum(case 季度 when '+ltrim(季度)+' then 销售额 else 0 end)' from #T group by 季度
exec ('select '+@SQL+' from #T group by 商品名称')--> 2005
select
商品名称,
第1季度销售额=isnull([1],0),
第2季度销售额=isnull([2],0),
第3季度销售额=isnull([3],0),
第4季度销售额=isnull([4],0)
from
(select * from #T) a
pivot
(max(销售额) for 季度 in ([1],[2],[3],[4])) b/*
商品名称 第1季度销售额 第2季度销售额 第3季度销售额 第4季度销售额
-------- ------------- ------------- ------------- -------------
a 1999 3333 0 3434
b 3345 3434 4564 0
*/
(case when b.a3 is null then 0 else b.a3 end),
(case when c.a3 is null then 0 else c.a3 end),
(case when d.a3 is null then 0 else d.a3 end),
(case when e.a3 is null then 0 else e.a3 end)
from (select a1 from tb1) a
left join (select a1,a3 from tb1 where a2=1) b on a.a1=b.a1
left join (select a1,a3 from tb1 where a2=2) c on a.a1=c.a1
left join (select a1,a3 from tb1 where a2=3) d on a.a1=d.a1
left join (select a1,a3 from tb1 where a2=4) e on a.a1=e.a1
a1:商品名称
a2:季度
a3:销售额 大数据量估计效率不高，而且在access里语句报错