行专列

最新推荐文章于 2024-05-11 09:52:18 发布

转载最新推荐文章于 2024-05-11 09:52:18 发布 · 416 阅读

文章标签：

#2010 #table #insert #object #null #sql

sql 专栏收录该内容

7 篇文章

订阅专栏

行变列，原视图如下：

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 行受影响)