34,593
社区成员
发帖
与我相关
我的任务
分享
group by name,year
--改为 group by left(name,5),year试一下
select name,year,
max(case month when 1 then num esle 0 end)一月份num,
max(case month when 2 then num esle 0 end)二月份num,
max(case month when 3 then num esle 0 end)二月份num,
.....
from tbName
group by name,year
--行列转化
if object_id('test') is not null
drop table test
go
create table test
(
name varchar(10),
year int,
month int,
num int
)
go
insert test
select 'test01',2012,1,20 union all
select 'test01',2012,2,20 union all
select 'test01',2012,2,20 union all
select 'test01',2012,3,20 union all
select 'test01',2012,4,20 union all
select 'test01',2012,4,20 union all
select 'test01',2012,4,20 union all
select 'test01',2012,5,20 union all
select 'test01',2012,5,20 union all
select 'test01',2012,6,20 union all
select 'test01',2012,7,20 union all
select 'test01',2012,7,20 union all
select 'test01',2012,7,20 union all
select 'test01',2012,7,20 union all
select 'test01',2012,7,20 union all
select 'test01',2012,8,20 union all
select 'test01',2012,9,20 union all
select 'test01',2012,10,20 union all
select 'test01',2012,11,20 union all
select 'test01',2012,12,20
go
select
name,
year,
sum(case when month=1 then num else 0 end) as [1月份num],
sum(case when month=2 then num else 0 end) as [2月份num],
sum(case when month=3 then num else 0 end) as [3月份num],
sum(case when month=4 then num else 0 end) as [4月份num],
sum(case when month=5 then num else 0 end) as [5月份num],
sum(case when month=6 then num else 0 end) as [6月份num],
sum(case when month=7 then num else 0 end) as [7月份num],
sum(case when month=8 then num else 0 end) as [8月份num],
sum(case when month=9 then num else 0 end) as [9月份num],
sum(case when month=10 then num else 0 end) as [10月份num],
sum(case when month=11 then num else 0 end) as [11月份num],
sum(case when month=12 then num else 0 end) as [12月份num]
from
test
group by
name,year
/*
name year 1月份num 2月份num 3月份num 4月份num 5月份num 6月份num 7月份num 8月份num 9月份num 10月份num 11月份num 12月份num
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
test01 2012 20 40 20 60 40 20 100 20 20 20 20 20
(1 行受影响)
*/