[MSSQL]SQL server 2005中竖排变横排显示 收藏

SQL server 2005中竖排变横排显示 收藏 

create table r(num varchar(4),[time] varchar(10),rc int,dc int)

go

insert r

select 1,'2009-07-26',100,200 union all

select 3,'2009-07-25',87,352 union all

select 3,'2009-07-26',121,45 union all

select 2,'2009-07-26',100,76 union all

select 1,'2009-07-01',87,68 union all

select 1,'2009-07-02',121,89 union all

select 1,'2009-07-03',10,102 union all

select 4,'2009-07-23',50,32

go

 

CREATE PROCEDURE [dbo].[up_Test] 

@StartDate DATETIME, --查询开始日期 

@EndDate DATETIME --查询结束日期 

AS 

DECLARE @tmpCondition VARCHAR(200) 

DECLARE @tmpsql VARCHAR(MAX) 

DECLARE @CurrDate DATETIME 

DECLARE @tmpDate VARCHAR(10) 

SET @EndDate=DateAdd(Day,1,@EndDate)

--如果数据量较大建议使用临时表

SET @tmpCondition ='WHERE T1.[time]>='''+ CONVERT(VARCHAR(10),@StartDate,120)  +''' AND T1.[time]<'''+ CONVERT(VARCHAR(10),@EndDate,120)  +'''' 

SET @tmpsql='' 

SET     @CurrDate = DateAdd(Day,-30, Convert(Varchar(10), Getdate(), 120)) 

WHILE   @CurrDate <= DateAdd(Day,-1, Convert(Varchar(10), Getdate(), 120)) 

BEGIN  

    SET @tmpDate = CONVERT(VARCHAR(10),@CurrDate,120) 

    SET @tmpsql = @tmpsql + ', CAST(SUM(CASE WHEN [time] = '''+@tmpDate+''' THEN T1.dc ELSE 0 END) AS INT) AS ''['+CONVERT(VARCHAR(10),@CurrDate,120)+']配发'''  

    SET @CurrDate = @CurrDate +1 

END 

SET     @CurrDate = DateAdd(Day,-30, Convert(Varchar(10), Getdate(), 120)) 

WHILE   @CurrDate <= DateAdd(Day,-1, Convert(Varchar(10), Getdate(), 120)) 

BEGIN  

    SET @tmpDate = CONVERT(VARCHAR(10),@CurrDate,120) 

    SET @tmpsql = @tmpsql + ', CAST(SUM(CASE WHEN [time] = '''+@tmpDate+''' THEN T1.rc ELSE 0 END) AS INT) AS ''['+CONVERT(VARCHAR(10),@CurrDate,120)+']销售'''  

    SET @CurrDate = @CurrDate +1 

END 

SET @tmpsql= 'SELECT ISNULL(T1.num,''合计'') AS ID,sum(dc) as dc ,sum(rc) as rc '+@tmpsql  

          +' FROM r T1  GROUP BY 

           T1.num WITH ROLLUP ' 

PRINT @tmpsql 

EXEC (@tmpsql)

go

exec up_Test N'2009-01-01','2009-06-01'

drop table r

drop proc up_Test