create proc proc_total
@begindate char(10),
@enddate char(10)
as
--declare @begindate char(10)
--declare @enddate char(10)
--set @begindate='2012.02.01'
--set @enddate='2013.01.01'
select tablea.*,tableb.dept as 备注 from (select distinct u_id, a.u_emid,a.u_name,left(@begindate,7)+'-'+left(@enddate,7) as 时间段,
 sum(ep_money) as 消费汇总
 from userinfo a join expense b on a.u_id=b.ep_u_id where 
left(@begindate,7)<=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) 
and left(@enddate,7)>=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) 
group by u_id, a.u_emid,a.u_name)tablea join 
(select distinct ep_u_id,dept= stuff((select',' + ep_year+'.'+ep_month+'消费'+cast(ep_money as varchar(20)) from(select ep_u_id,ep_year,ep_month,sum(ep_money) as ep_money from expense where 
left(@begindate,7)<=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) 
and left(@enddate,7)>=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) group by ep_u_id,ep_year,ep_month
)a where a.ep_u_id=b.ep_u_id  for xml path('')),1,1,'')
from 
(select ep_u_id,ep_year,ep_month,sum(ep_money) as ep_money from expense where 
left(@begindate,7)<=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) 
and left(@enddate,7)>=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) group by ep_u_id,ep_year,ep_month
)b)tableB on tablea.u_id=tableb.ep_u_id

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

SQL code

go
create table userinfo (u_id int,u_name varchar(4),u_emid int)
insert into userinfo
select 1,'张三',123 union all
select 2,'李四',124 union all
select 3,'王五',125

go
create table expense (ep_id int,ep_u_id int,ep_year int,ep_month int,ep_money int)
insert into expense
select 71,3,2012,3,1300 union all
select 70,2,2012,3,1200 union all
select 69,1,2012,3,1200 union all
select 37,3,2012,2,800 union all
select 36,2,2012,2,1100 union all
select 35,1,2012,2,900 union all
select 3,3,2012,1,1000 union all
select 2,2,2012,1,1200 union all
select 1,1,2012,1,1100

go

create proc proc_expense
(
    @username varchar(20),
    @begintime datetime,
    @endtime datetime
)
as
begin
    ;with maco as(
    select 
        ep_u_id as u_emid,
        (select top 1 u_name from userinfo where u_id=t.ep_u_id) as u_name,
         replace(convert(varchar(7),@begintime,111)+'-'+convert(varchar(7),@endtime,111),'/','.') as 时间段,
        (select sum(ep_money) from expense where ep_u_id=t.ep_u_id) as 消费汇总,