SQL交叉报表

 

在数据库板块看到一个问题：

col1	col2	col3
a	100	2009-1-5
a	80	2009-1-8
b	300	2009-1-9
b	60	2009-1-7
c	100	2009-1-9
c	150	2009-1-10

根据日期区间查询(例如查询 2009/1/4到2009/1/10)得到以下结果

col1	2009-1-4	2009-1-5	2009-1-6	2009-1-7	2009-1-8	2009-1-9	2009-1-10
a	0	100	0	0	80	0	0
b	0	0	0	60	0	300	0
c	0	0	0	0	0	100	150

这是一个数据库交叉报表的问题，经常碰到的是固定的列，但是这个问题中，日期是不固定的，查询间隔可多可少，具体实现方法如下。

 

 create table t
(
 col1 varchar(10),
 col2 int,
 col3 datetime
)

insert into t (col1,col2,col3)values('a',100,'2009-1-5')
insert into t (col1,col2,col3)values('a',80,'2009-1-8')
insert into t (col1,col2,col3)values('b',300,'2009-1-9')
insert into t (col1,col2,col3)values('b',60,'2009-1-7')
insert into t (col1,col2,col3)values('c',100,'2009-1-9')
insert into t (col1,col2,col3)values('c',150,'2009-1-10')

declare @beginDate datetime　--查询开始时间
declare @endDate datetime　--查询结束时间
declare @sql varchar(8000)
set @beginDate = cast('2009-1-4' as datetime)
set @endDate = cast('2009-1-10' as datetime)
set @sql = 'select col1,'
while (datediff(d,@beginDate,@endDate)>0)　--循环添加日期列
begin
 set @sql = @sql + 'sum(case col3 when '''+cast(@beginDate as varchar)+''' then col2 else 0 end) as '''+convert(varchar(10),@beginDate,120)+''','
 set @beginDate = dateadd(d,1,@beginDate)
end
set @sql = @sql + 'sum(case col3 when '''+cast(@beginDate as varchar)+''' then col2 else 0 end) as '''+convert(varchar(10),@beginDate,120)+''' '
set @sql = @sql + 'from t group by col1'

exec(@sql)

执行结果

------------------------
  col1 2009-01-04 2009-01-05 2009-01-06 2009-01-07 2009-01-08 2009-01-09 2009-01-10
1 a         0         100         0          0          80         0          0
2 b         0          0          0          60         0          300        0
3 c         0          0          0          0          0          100        150