行专列

行变列，原视图如下：

 

 

 

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