if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[monthlj]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[monthlj]
GO

CREATE TABLE [dbo].[monthlj] (
    [采集日期] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [甲线煤量累计] [real] NULL ,
    [甲线石灰石累计] [real] NULL ,
    [甲线耗电量累计] [real] NULL ,
    [乙线煤量累计] [real] NULL ,
    [乙线石灰石累计] [real] NULL ,
    [乙线耗电量累计] [real] NULL ,
    [负荷1累计] [real] NULL ,
    [负荷2累计] [real] NULL ,
    [负荷3累计] [real] NULL 
) ON [PRIMARY]
GO

CREATE PROCEDURE p_monthlj_Year(
    @dt int
)
SELECT MONTH([采集日期]) AS 月份,
SUM([甲线煤量累计]) AS [甲线煤量累计],
............

FROM dbo].[monthlj] 
WHERE YEAR([采集日期])=@dt
GROUP BY MONTH([采集日期])

go
--調用
EXEC p_monthlj_Year 2011

------解决方案--------------------

探讨


采集日期 甲线煤量累计
20110928 21	  23	   25	31	33	35	41	43	45
20110929 675.     59.65287 57.43	1012.8	89.63	82.94	584.5	526.6	468.8
20110930 32.7	  1254.83  9	60.24062	367.1203	101200  657
1000028	454.9	 ……


------解决方案--------------------

SQL code
select 
left([采集日期],6) as 年月,
SUM(isnull([甲线煤量累计],0)) AS [甲线煤量累计],
SUM(isnull([甲线石灰石累计],0)) AS [甲线煤量累计],
...
SUM(isnull([负荷2累计],0)) AS [负荷2累计],
SUM(isnull([负荷3累计],0)) AS [负荷3累计]
from dbo].[monthlj] 
group by left([采集日期],6)
order by left([采集日期],6)

------解决方案--------------------

SQL code
select 
  sum(case datepart(mm,采集日期) when 1 then 甲线煤量累计 else 0 end) [甲线煤量累计_01月],
  sum(case datepart(mm,采集日期) when 2 then 甲线煤量累计 else 0 end) [甲线煤量累计_02月],
  ...
  sum(case datepart(mm,采集日期) when 12 then 甲线煤量累计 else 0 end) [甲线煤量累计_12月],
  如果还需要合计其他字段则如上写法即可。
from monthlj
where datepart(yy,采集日期) = 2011

------解决方案--------------------
SQL code

Select Convert(Varchar(7),采集日期,120) As 月份,
    Sum(isNull(甲线煤量累计,0)) As 甲线煤量累计,
    Sum(isNull(甲线石灰石累计,0)) As 甲线石灰石累计,
    Sum(isNull(甲线耗电量累计,0)) As 甲线耗电量累计,
    Sum(isNull(乙线煤量累计,0)) As 乙线煤量累计,
    Sum(isNull(乙线石灰石累计,0)) As 乙线石灰石累计,
    Sum(isNull(乙线耗电量累计,0)) As 乙线耗电量累计,
    Sum(isNull(负荷1累计,0)) As 负荷1累计,
    Sum(isNull(负荷2累计,0)) As 负荷2累计,
    Sum(isNull(负荷3累计,0)) As 负荷3累计
From monthlj
Where Convert(Varchar(4),采集日期,120) = '2011'
Group By Convert(Varchar(7),采集日期,120)

------解决方案--------------------
SQL code
select 
  sum(case when datepart(mm,采集日期)=1 then 甲线煤量累计 else 0 end) as '甲线煤量累计_01月',
  sum(case when datepart(mm,采集日期)=2 then 甲线煤量累计 else 0 end) as '甲线煤量累计_02月',
  ...
from
 monthlj
where
 datepart(yy,采集日期) = 2011