有个sql查询语句请教下。
情况是这样的，有个表有四个属性，分为名称，年份，月份，数量 name,year,month,num
我现在是要查询一年中12个月的数量明细。名称是固定的10个值，需要查询出的结果如下：
name,year,一月份num,二月份num，三月份num.......十二月份num
请问这个该咋整。
[解决办法]
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 行受影响)
*/