行转列汇总

[飞驴]

产品编号 区间 销量
10001 201203 30
10001 201201 40
10001 201202 50
10002 201201 10
10002 201202 20
10002 201203 30

如何将上述数据写入到，现有如下的数据表中
如果本月是2月份
产品 M-1月 M月 M+1月
10001 40 50 30
10002 10 20 30

[开着拖拉机泡妞]

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([产品编号] int,[区间] varchar(6),[销量] int)
insert [tbl]
select 10001,'201203',30 union all
select 10001,'201201',40 union all
select 10001,'201202',50 union all
select 10002,'201201',10 union all
select 10002,'201202',20 union all
select 10002,'201203',30

declare @str varchar(max)
set @str=''
select @str=@str+','+'['+RIGHT([区间],2)+'月]'+'=max(case when RIGHT([区间],2)='
+QUOTENAME(RIGHT([区间],2),'''')+' then [销量] else 0 end)' from tbl
group by RIGHT([区间],2)
exec('select [产品编号]'+@str+' from tbl group by [产品编号]')

/*
产品编号	01月	02月	03月
10001	40	50	30
10002	10	20	30
*/

[开着拖拉机泡妞]

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([产品编号] int,[区间] varchar(6),[销量] int)
insert [tbl]
select 10001,'201203',30 union all
select 10001,'201201',40 union all
select 10001,'201202',50 union all
select 10002,'201201',10 union all
select 10002,'201202',20 union all
select 10002,'201203',30 union all
select 10002,'201204',30 union all
select 10001,'201203',30


go
if OBJECT_ID('pro_tracy')is not null
drop proc pro_tracy
go
create proc pro_tracy
as
create table #t
(
[产品编号] int,
[区间] varchar(6),
[销量] int
)
insert #t
select * from tbl 
where cast(RIGHT([区间],2) as Int) 
between MONTH(GETDATE())-1 and MONTH(GETDATE())+1
declare @str varchar(max)
set @str=''
select @str=@str+','+'['+RIGHT([区间],2)+'月]'+'=max(case when RIGHT([区间],2)='
+QUOTENAME(RIGHT([区间],2),'''')+' then [销量] else 0 end)' from #t
group by RIGHT([区间],2)
exec('select [产品编号]'+@str+' from #t group by [产品编号]')

--修改了你的数据
exec pro_tracy
/*
产品编号	02月	03月	04月
10001	50	30	0
10002	20	30	30
*/