求教sql 行转列问题
有这样一个表：
year month monthTotal
201211 6
201212 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 )
FROM huang
GROUP BY [year]
----------------结果----------------------------
/*
YEAR 01 02 03 04 05 06 07 08 09 10 11 12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010 24 0 0 0 0 0 0 0 0 0 0 0
2011 0 0 9 0 0 0 0 20 0 0 0 0
2012 0 0 0 0 0 0 0 0 0 0 6 13
(3 行受影响)
*/
select year as year,
max(case month when '01' then monthTotal else 0 end) '01',
max(case month when '02' then monthTotal else 0 end) '02',
max(case month when '03' then monthTotal else 0 end) '03',
max(case month when '04' then monthTotal else 0 end) '04',
max(case month when '05' then monthTotal else 0 end) '05',
max(case month when '06' then monthTotal else 0 end) '06',
max(case month when '07' then monthTotal else 0 end) '07',
max(case month when '08' then monthTotal else 0 end) '08',
max(case month when '09' then monthTotal else 0 end) '09',
max(case month when '10' then monthTotal else 0 end) '10',
max(case month when '11' then monthTotal else 0 end) '11',
max(case month when '12' then monthTotal else 0 end) '12',
SUM(monthTotal) 'yearTotal'
from 表名
group by year
USE test
GO
-->生成表t1
if object_id(N't1') is not null
drop table [t1]
Go
Create table [t1]([year] nvarchar(4),[month] nvarchar(2),[monthTotal] smallint)
Insert into [t1]
Select '2012',N'11',6
Union all Select '2012',N'12',13
Union all Select '2011',N'08',20
Union all Select '2011',N'03',9
Union all Select '2010',N'01',24
DECLARE @sql NVARCHAR(MAX),@sql_col NVARCHAR(MAX)
SELECT @sql_col=ISNULL(@sql_col+',','')+'Max(Case When [month]='''+RIGHT(100+number,2)+''' Then [monthTotal] Else 0 End) As ['+RIGHT(100+number,2)+']' FROM master.dbo.spt_values AS a,t1 AS b
WHERE a.type='P'
GROUP BY number
HAVING a.number >= MIN(b.[month]) AND a.number <= MAX(b.[month])
Exec ('Select [year],'+@sql_col+',Sum([monthTotal]) As yearTotal From t1 Group by [year] Order by [year] Desc')
/*
Result:
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
2011 0 0 9 0 0 0 0 20 0 0 0 0 29
2010 24 0 0 0 0 0 0 0 0 0 0 0 24
========================
Data:
Insert into [t1]
Select '2012',N'11',6
Union all Select '2012',N'12',13
Union all Select '2011',N'08',20
Union all Select '2011',N'03',9
Union all Select '2010',N'01',24
Result:
year 03 04 05 06 07 08 yearTotal
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2011 9 0 0 0 0 20 29
*/
select year as year,
sum(case month when '01' then monthTotal else 0 end) as '01',
sum(case month when '02' then monthTotal else 0 end) as '02',
sum(case month when '03' then monthTotal else 0 end) as '03',
sum(case month when '04' then monthTotal else 0 end) as '04',
sum(case month when '05' then monthTotal else 0 end) as '05',
sum(case month when '06' then monthTotal else 0 end) as '06',
sum(case month when '07' then monthTotal else 0 end) as '07',
sum(case month when '08' then monthTotal else 0 end) as '08',
sum(case month when '09' then monthTotal else 0 end) as '09',
sum(case month when '10' then monthTotal else 0 end) as '10',
sum(case month when '11' then monthTotal else 0 end) as '11',
sum(case month when '12' then monthTotal else 0 end) as '12',
sum(monthTotal) as 'yearTotal'
from 表名
group by year
