sql 行转置查询
现有数据如下：
日期类型为date,金额类型为float
日期 金额
2014-1-1 10
2014-1-1 20
2014-3-1 11
2014-3-1 21
2014-4-1 32
2014-5-1 22
2014-6-1 2
2014-8-1 4
2014-11-1 6
2014-12-1 22
2013-1-1 13
2012-1-2 7
2013-2-1 6
现需要得到数据如下:根据年份查询出相应数据的合，如果没有该月份也要列出，但数据为0
年 2014-1 2014-2 2014-3 2014-4 2014-5 2014-6 2014-7 2014-8 2014-9 2014-10 2014-11 2014-12
30 0 32 32 22 2 0 4 0 0 6 22
2014
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-10 15:46:00
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([日期] date,[金额] float)
insert [huang]
select '2014-1-1',10 union all
select '2014-1-1',20 union all
select '2014-3-1',11 union all
select '2014-3-1',21 union all
select '2014-4-1',32 union all
select '2014-5-1',22 union all
select '2014-6-1',2 union all
select '2014-8-1',4 union all
select '2014-11-1',6 union all
select '2014-12-1',22 union all
select '2013-1-1',13 union ALL
select '2012-1-2',7 union all
select '2013-2-1',6
--------------开始查询--------------------------
/*
create function generateTimeV2
(
@begin_date date,
@end_date date
)
returns @t table(date date)
as
begin
insert into @t
select dateadd(dd,number,@begin_date) AS date
from master..spt_values
where type='p' and dateadd(dd,number,@begin_date)<=@end_date
return
end
*/
IF OBJECT_ID('tempdb..#t','u')IS NOT NULL
DROP TABLE #t
SELECT ISNULL(a.[金额], 0) [金额] ,
b.[date] INTO #t
FROM [huang] a
RIGHT JOIN ( SELECT *
FROM dbo.generateTimeV2('2013-12-01', '2014-12-31')
) b ON a.[日期] = b.[date]
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(LEFT([date],7))+'=sum(case when LEFT([date],7)='+quotename(LEFT([date],7),'''')+' then [金额] else 0 end)'
from #t group by LEFT([date],7)
exec('select left([date],4) [年份]'+@s+' from #t group by left([date],4)')
/*
年份 2013-12 2014-01 2014-02 2014-03 2014-04 2014-05 2014-06 2014-07 2014-08 2014-09 2014-10 2014-11 2014-12
-------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
2013 0 0 0 0 0 0 0 0 0 0 0 0 0
2014 0 30 0 32 32 22 2 0 4 0 0 6 22
*/
