case when扩展列用法研究

USE [DB_TL]
GO
/****** Object: StoredProcedure [dbo].[P_totleWaterYear] Script Date: 03/06/2015 15:38:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,liujun>
-- Create date: <Create Date,,2015-02-05>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[P_totleWaterYear]
@ryear varchar(4),
--@rmonth varchar(2),
@manageDep varchar(500)
AS
BEGIN
declare @rdate varchar(20)
declare @etime varchar(20)
declare @btime varchar(20)

set @btime=@ryear+'-'+'01'
set @etime=@ryear+'-'+'12'

declare @sql varchar(max)
set @sql = ' select day(SPTIME) as riqi'
select @sql = @sql + ' ,case when sum(case MONTH(SPTIME) when ''' + Convert(varchar,a.ssptime) + ''' then WR_WATER_C.USEWATER else 0.0 end)is null then sum(case MONTH(SPTIME) when ''' + Convert(varchar,a.ssptime) + ''' then WR_WATER_C.USEWATER else 0.0 end) else sum(case MONTH(SPTIME) when ''' + Convert(varchar,a.ssptime) + ''' then WR_WATER_C.USEWATER else 0.0 end) end [' +Convert(varchar,a.ssptime) + ']'
from (select distinct MONTH(SPTIME) as ssptime from WR_WATER_C where CONVERT(varchar(7),SPTIME,120)>=@btime and CONVERT(varchar(7),SPTIME,120)<=@etime ) as a order by a.ssptime
set @sql = @sql + ',sum(WR_WATER_C.USEWATER) as hjwq from WR_WATER_C left join WR_ICCARD_B on WR_WATER_C.FCARD=WR_ICCARD_B.FCARD left join WR_WUSER_B on WR_WUSER_B.FCD=WR_ICCARD_B.FCD left join B_Cun on B_Cun.Cun_No=WR_WUSER_B.ADDVCD left join B_Xiang on B_Xiang.Xiang_No=B_Cun.Xiang_No where B_Xiang.Xiang_No in ('+@manageDep+') and CONVERT(varchar(7),SPTIME,120)>='''+@btime+''' and CONVERT(varchar(7),SPTIME,120)<='''+@etime+''' '
set @sql = @sql + ' group by SPTIME order by day(SPTIME) asc'

exec(@sql)

END