比较头疼的问题，召唤邹建大哥。。。。

[mobydick]

最近有个问题比较头疼，希望能帮一下忙啊。

a表，两列，第一列datetime类型，第二列int类型

要根据a表生成一个有14列的查询结果，第一列varchar类型，存放datetime中存在的年份，需要排序；第二到第十四列int类型，分别存放一月到十二月和合计的数据。如果a表中没有当月数据，就为零。
如图
+----+----+----+----+----+----+----+----+----+----+----+------+------+----+
|年份|一月|二月|三月|四月|五月|六月|七月|八月|九月|十月|十一月|十二月|合计|
+----+----+----+----+----+----+----+----+----+----+----+------+------+----+
| | | | | | | | | | | | | | |
+----+----+----+----+----+----+----+----+----+----+----+------+------+----+
| | | | | | | | | | | | | | |
+----+----+----+----+----+----+----+----+----+----+----+------+------+----+

多谢各位捧场啊。

[mobydick]

又多了一个问题。
还是像上面的一样，但是不是生成14列，只有3列数据，第一列是Year，第二列Month，第三列是值。要把第二列中没有数据的月份值补上。
比如第一张表只有一行数据
2005-1-1|16
结果就是
2005|1|16
2005|2|0
2005|3|0
2005|4|0
......
2005|12|0

[mobydick]

多谢几位大哥的回答啊。
又学到不少东西啊。
还是要大家多帮忙啊。谢谢：）

[mengzulin]

select [year]=year([date]),[month]=month([date]),[value] =sum([value]) from tablename
group by year([date]),[month]=month([date])

[ljian_mail]

不要动不动就麻烦邹健大哥好不好

[Frewin]

Select Year(dateCol),Case When Month(dateCol)=1 Then 数字列 End, Case When Month(dateCol)=2 Then 数字列 End,........
(Select Sum(datecol) From tb Where Year(dateCol)=a.Year(dateCol)) From tb a

[Frewin]

Select Year(dateCol),Case When Month(dateCol)=1 Then 数字列 End, Case When Month(dateCol)=2 Then 数字列 End,
Case When Month(dateCol)=3 Then 数字列 End, Case When Month(dateCol)=4 Then 数字列 End,
Case When Month(dateCol)=5 Then 数字列 End, Case When Month(dateCol)=6 Then 数字列 End,
Case When Month(dateCol)=7 Then 数字列 End, Case When Month(dateCol)=8 Then 数字列 End,
Case When Month(dateCol)=9 Then 数字列 End, Case When Month(dateCol)=10 Then 数字列 End,
Case When Month(dateCol)=11 Then 数字列 End, Case When Month(dateCol)=12 Then 数字列 End,
(Select Sum(datecol) From tb Where Year(dateCol)=a.Year(dateCol)) From tb a
Group By a.Year(dateCol)

[mengzulin]

select cast(yaer(datecol)) as 年份,sum(case month(datecol) when 1 then value else 0 end) as 一月,sum(case month(datecol) when 2 then value else 0 end) as 二月,sum(case month(datecol) when 3 then value else 0 end) as 三月,sum(case month(datecol) when 4 then value else 0 end) as 四月,sum(case month(datecol) when 5 then value else 0 end) as 五月,sum(case month(datecol) when 6 then value else 0 end) as 六月,sum(case month(datecol) when 7 then value else 0 end) as 七月,sum(case month(datecol) when 8 then value else 0 end) as 八月,sum(case month(datecol) when 9 then value else 0 end) as 九月,sum(case month(datecol) when 10 then value else 0 end) as 十月,sum(case month(datecol) when 11 then value else 0 end) as 十一月,sum(case month(datecol) when 1 then value else 0 end2) as 十二月,sum(value ) as 合计 from table
group by cast(yaer(datecol))
order by cast(yaer(datecol))

[子陌红尘]

select
年份 = year(col1),
一月 = sum(case month(col1) when 1 then col2 else 0 end),
二月 = sum(case month(col1) when 2 then col2 else 0 end),
三月 = sum(case month(col1) when 3 then col2 else 0 end),
四月 = sum(case month(col1) when 4 then col2 else 0 end),
五月 = sum(case month(col1) when 5 then col2 else 0 end),
六月 = sum(case month(col1) when 6 then col2 else 0 end),
七月 = sum(case month(col1) when 7 then col2 else 0 end),
八月 = sum(case month(col1) when 8 then col2 else 0 end),
九月 = sum(case month(col1) when 9 then col2 else 0 end),
十月 = sum(case month(col1) when 10 then col2 else 0 end),
十一月 = sum(case month(col1) when 11 then col2 else 0 end),
十二月 = sum(case month(col1) when 12 then col2 else 0 end),
合计 = sum(col2)
from
a
group by
year(col1)
order by
year(col1)

[kylike]

-- 是一个关于交叉报表的问题。如果邹大哥忙的话，我且代答了：
-- 举例：

CREATE Table _yes (Term smalldatetime , Num int )

insert into _yes (Term, Num)
Select '2004-1-2' , 3 union
Select '2005-1-1' , 1 Union
Select '2004-1-1' , 2 Union
Select '2004-2-1' , 2 Union
Select '2004-3-1' , 2 Union
Select '2004-4-1' , 2 Union
Select '2004-5-1' , 2 Union
Select '2004-6-1' , 2 Union
Select '2004-7-1' , 2 Union
Select '2004-8-1' , 2 Union
Select '2004-9-1' , 2 Union
Select '2004-10-1' , 2 Union
Select '2004-11-1' , 2 Union
Select '2004-12-1' , 2 Union
Select '2005-1-1' , 1

GO


Select year(a.Term) 年份,
Sum(Case when datepart(Month,a.term) = 1 then Num end ) as 一月
,Sum(Case when datepart(Month,a.term) = 2 then Num end ) as 二月
,Sum(Case when datepart(Month,a.term) = 3 then Num end ) as 三月
,Sum(Case when datepart(Month,a.term) = 4 then Num end ) as 四月
,Sum(Case when datepart(Month,a.term) = 5 then Num end ) as 五月
,Sum(Case when datepart(Month,a.term) = 6 then Num end ) as 六月
,Sum(Case when datepart(Month,a.term) = 7 then Num end ) as 七月
,Sum(Case when datepart(Month,a.term) = 8 then Num end ) as 八月
,Sum(Case when datepart(Month,a.term) = 9 then Num end ) as 九月
,Sum(Case when datepart(Month,a.term) = 10 then Num end ) as 十月
,Sum(Case when datepart(Month,a.term) = 11 then Num end ) as 十一月
,Sum(Case when datepart(Month,a.term) = 12 then Num end ) as 十二月
,Sum(a.Num) as 合计
From _yes a group by year(a.Term)

GO
-- Drop table _yes

[summerICEREDTEA]

交叉表 。。
楼上的都说完了 -____-

[zjcxc]

按楼上几位的可以解决