出库表结构：
CREATE TABLE [dbo].[半成品出库](
[id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[单据日期] [datetime] NOT NULL,
[产品货号] [nvarchar](50) NOT NULL,
[出库数量] [decimal](18, 2) NULL
CONSTRAINT [PK__半成品出库__3213E83F7073AF84] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
数据内容：
SET IDENTITY_INSERT [dbo].[半成品出库] ON
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(1 AS Decimal(18, 0)), CAST(0x0000A35F00000000 AS DateTime), N'aa', CAST(100.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(2 AS Decimal(18, 0)), CAST(0x0000A36E00000000 AS DateTime), N'aa', CAST(300.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(3 AS Decimal(18, 0)), CAST(0x0000A38600000000 AS DateTime), N'aa', CAST(100.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(4 AS Decimal(18, 0)), CAST(0x0000A38D00000000 AS DateTime), N'bb', CAST(150.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(5 AS Decimal(18, 0)), CAST(0x0000A3A200000000 AS DateTime), N'aa', CAST(50.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(6 AS Decimal(18, 0)), CAST(0x0000A3AA00000000 AS DateTime), N'aa', CAST(500.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(7 AS Decimal(18, 0)), CAST(0x0000A3B100000000 AS DateTime), N'aa', CAST(300.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(8 AS Decimal(18, 0)), CAST(0x0000A3B300000000 AS DateTime), N'bb', CAST(200.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(9 AS Decimal(18, 0)), CAST(0x0000A3BB00000000 AS DateTime), N'aa', CAST(100.00 AS Decimal(18, 2)))
INSERT [dbo].[半成品出库] ([id], [单据日期], [产品货号], [出库数量]) VALUES (CAST(10 AS Decimal(18, 0)), CAST(0x0000A3C000000000 AS DateTime), N'bb', CAST(100.00 AS Decimal(18, 2)))
SET IDENTITY_INSERT [dbo].[半成品出库] OFF
id 单据日期 产品货号 出库数量
1 2014-07-05 aa 100.00
2 2014-07-20 aa 300.00
3 2014-08-13 aa 100.00
4 2014-08-20 bb 150.00
5 2014-09-10 aa 50.00
6 2014-09-18 aa 500.00
7 2014-09-25 aa 300.00
8 2014-09-27 bb 200.00
9 2014-10-05 aa 100.00
10 2014-10-10 bb 100.00
现在想查询每月的出库量合计
select years as '年份', 产品货号,
case when months=1 then 出库合计 else 0 end '1月份',
case when months=2 then 出库合计 else 0 end '2月份',
case when months=3 then 出库合计 else 0 end '3月份',
case when months=4 then 出库合计 else 0 end '4月份',
case when months=5 then 出库合计 else 0 end '5月份',
case when months=6 then 出库合计 else 0 end '6月份',
case when months=7 then 出库合计 else 0 end '7月份',
case when months=8 then 出库合计 else 0 end '8月份',
case when months=9 then 出库合计 else 0 end '9月份',
case when months=10 then 出库合计 else 0 end '10月份',
case when months=11 then 出库合计 else 0 end '11月份',
case when months=12 then 出库合计 else 0 end '12月份'
from(
select datepart(yy,单据日期) as years, 产品货号,SUM(出库数量) as 出库合计,datepart(mm,单据日期) months
from 半成品出库 with(nolock) group by year(单据日期),month(单据日期),产品货号
)as test
查询结果：
年份 产品货号 1月份 2月份 3月份 4月份 5月份 6月份 7月份 8月份 9月份 10月份 11月份 12月份
2014 aa 0.00 0.00 0.00 0.00 0.00 0.00 400.00 0.00 0.00 0.00 0.00 0.00
2014 aa 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 0.00 0.00 0.00
2014 bb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 150.00 0.00 0.00 0.00 0.00
2014 aa 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 850.00 0.00 0.00 0.00
2014 bb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 200.00 0.00 0.00 0.00
2014 aa 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 0.00
2014 bb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 0.00
我想变成这样的效果，SQL应该怎么写
年份 产品货号 1月份 2月份 3月份 4月份 5月份 6月份 7月份 8月份 9月份 10月份 11月份 12月份
2014 aa 0.00 0.00 0.00 0.00 0.00 0.00 400.00 100.00 850.00 100.00 0.00 0.00
2014 bb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 150.00 200.00 100.00 0.00 0.00
请各位高手帮帮忙，我是新手对SQL不是很熟悉，谢谢！
------解决思路----------------------
select years as '年份', 产品货号,
SUM(case when months=1 then 出库合计 else 0 END) '1月份',
SUM(case when months=2 then 出库合计 else 0 END) '2月份',
SUM(case when months=3 then 出库合计 else 0 END) '3月份',
SUM(case when months=4 then 出库合计 else 0 END) '4月份',
SUM(case when months=5 then 出库合计 else 0 END) '5月份',
SUM(case when months=6 then 出库合计 else 0 END) '6月份',
SUM(case when months=7 then 出库合计 else 0 END) '7月份',
SUM(case when months=8 then 出库合计 else 0 END) '8月份',
SUM(case when months=9 then 出库合计 else 0 END) '9月份',
SUM(case when months=10 then 出库合计 else 0 END) '10月份',
SUM(case when months=11 then 出库合计 else 0 END) '11月份',
SUM(case when months=12 then 出库合计 else 0 END) '12月份'
from(
select datepart(yy,单据日期) as years, 产品货号,SUM(出库数量) as 出库合计,datepart(mm,单据日期) months
from 半成品出库 with(nolock) group by year(单据日期),month(单据日期),产品货号
)as test
GROUP BY years
------解决思路----------------------
select years as '年份', 产品货号,
SUM(case when months=1 then 出库合计 else 0 END) '1月份',
SUM(case when months=2 then 出库合计 else 0 END) '2月份',
SUM(case when months=3 then 出库合计 else 0 END) '3月份',
SUM(case when months=4 then 出库合计 else 0 END) '4月份',
SUM(case when months=5 then 出库合计 else 0 END) '5月份',
SUM(case when months=6 then 出库合计 else 0 END) '6月份',
SUM(case when months=7 then 出库合计 else 0 END) '7月份',
SUM(case when months=8 then 出库合计 else 0 END) '8月份',
SUM(case when months=9 then 出库合计 else 0 END) '9月份',
SUM(case when months=10 then 出库合计 else 0 END) '10月份',
SUM(case when months=11 then 出库合计 else 0 END) '11月份',
SUM(case when months=12 then 出库合计 else 0 END) '12月份'
from(
select datepart(yy,单据日期) as years, 产品货号,SUM(出库数量) as 出库合计,datepart(mm,单据日期) months
from 半成品出库 with(nolock) group by year(单据日期),month(单据日期),产品货号
)as test
GROUP BY years, 产品货号
GROUP BY 加聚合就可以了