有这样一个表：
year month monthTotal
2012 11 6
2012 12 13
2011 08 20
2011 03 9
2010 01 24
我现在要把每年的总计，和每月的monthTotal放在一行上，在mysql里面需要如何实现?
下面是我想要的结果：
year 01 02 03 04 05 06 07 08 09 10 11 12 yearTotal
2012 0 0 0 0 0 0 0 0 0 0 6 13 19
2111 0 0 9 0 0 0 0 20 0 0 0 0 29
求各位大神帮帮忙。
mysql?行转列?动态sql
------解决方案--------------------
你这个情况和下面例子很相似
行转列
------解决方案--------------------
我不知道mysql能不能用，这是SQLServer的
----------------------------
-- 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' THEN [monthTotal] ELSE 0 END )