日期:2014-05-17 浏览次数:20447 次
----------------------------
-- Author :DBA_Huangzj
-- Date :2013-01-08 11:33:15
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([year] int,[month] varchar(2),[monthTotal] int)
insert [huang]
select 2012,'11',6 union all
select 2012,'12',13 union all
select 2011,'08',20 union all
select 2011,'03',9 union all
select 2010,'01',24
--------------开始查询--------------------------
select * from [huang]
SELECT [YEAR],[01]=MAX(CASE WHEN [month]='01' THEN [monthTotal] ELSE 0 END ),
[02]=MAX(CASE WHEN [month]='02' THEN [monthTotal] ELSE 0 END) ,
[03]=MAX(CASE WHEN [month]='03' THEN [monthTotal] ELSE 0 END) ,
[04]=MAX(CASE WHEN [month]='04' THEN [monthTotal] ELSE 0 END) ,
[05]=MAX(CASE WHEN [month]='05' THEN [monthTotal] ELSE 0 END) ,
[06]=MAX(CASE WHEN [month]='06' THEN [monthTotal] ELSE 0 END) ,
[07]=MAX(CASE WHEN [month]='07' THEN [monthTotal] ELSE 0 END ),
[08]=MAX(CASE WHEN [month]='08' THEN [monthTotal] ELSE 0 END) ,
[09]=MAX(CASE WHEN [month]='09' THEN [monthTotal] ELSE 0 END) ,
[10]=MAX(CASE WHEN [month]='10' THEN [monthTotal] ELSE 0 END) ,
[11]=MAX(CASE WHEN [month]='11' THEN [monthTotal] ELSE 0 END) ,
[12]=MAX(CASE WHEN [month]='12' T