行变列,原视图如下:
-
SQL code
-
(物料编号) (年份) (月份) (数量) ITEM_CODE year month qty 0001 2010 5 500 0001 2010 5 400 0001 2010 5 200 0001 2010 6 800 0002 2010 7 1000 0002 2010 7 2000 0002 2010 8 5000
-
我想变为如下形式:
-
SQL code
-
ITEM_CODE 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 0001 0 0 0 0 1100 800 0 0 0 0 0 0 0002 0 0 0 0 0 0 3000 5000 0 0 0 0
-
-
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ITEM_CODE] varchar(4),[year] int,[month] int,[qty] int)
insert [tb]
select '0001',2010,5,500 union all
select '0001',2010,5,400 union all
select '0001',2010,5,200 union all
select '0001',2010,6,800 union all
select '0002',2010,7,1000 union all
select '0002',2010,7,2000 union all
select '0002',2010,8,5000
select ITEM_CODE,
sum(case when [month]=1 then qty else 0 end) as '1月',
sum(case when [month]=2 then qty else 0 end) as '2月',
sum(case when [month]=3 then qty else 0 end) as '3月',
sum(case when [month]=4 then qty else 0 end) as '4月',
sum(case when [month]=5 then qty else 0 end) as '5月',
sum(case when [month]=6 then qty else 0 end) as '6月',
sum(case when [month]=7 then qty else 0 end) as '7月',
sum(case when [month]=8 then qty else 0 end) as '8月',
sum(case when [month]=9 then qty else 0 end) as '9月',
sum(case when [month]=10 then qty else 0 end) as '10月',
sum(case when [month]=11 then qty else 0 end) as '11月',
sum(case when [month]=12 then qty else 0 end) as '12月'
from tb
group by ITEM_CODE
ITEM_CODE 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
--------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0001 0 0 0 0 1100 800 0 0 0 0 0 0
0002 0 0 0 0 0 0 3000 5000 0 0 0 0
(2 行受影响)
--> 测试数据: [t]
if object_id('[t]') is not null drop table [t]
go
create table [t] (ITEM_CODE varchar(4),year int,month int,qty int)
insert into [t]
select '0001',2010,5,500 union all
select '0001',2010,5,400 union all
select '0001',2010,5,200 union all
select '0001',2010,6,800 union all
select '0002',2010,7,1000 union all
select '0002',2010,7,2000 union all
select '0002',2010,8,5000
declare @s varchar(4000)
select @s=isnull(@s+',','')+'sum(case when month= '+ltrim(number)+' then qty else 0 end) ['+ltrim(number)+'月]'
from (select distinct month from [t]) tt
right join master..spt_values s
on month=number
where type='p' and number between 1 and 12
set @s='select ITEM_CODE,'+@s+' from t group by ITEM_CODE'
--print @s
exec(@s)
ITEM_CODE 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
--------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0001 0 0 0 0 1100 800 0 0 0 0 0 0
0002 0 0 0 0 0 0 3000 5000 0 0 0 0
(2 行受影响)