客户需求这样的结果，还要求可以按要求时间（T1）查询：
建立的数据表如下(Table_1)：
数据如下：
怎样才能把 table_1 这样的数据变成 T1这样的如下SQL
步骤如下：
1. 把当天不需要的数据去掉：如下：
select rooms,times,MAX(cb) as cb from(
select Rooms,STUFF(STUFF(CONVERT(char(8),MAX(times),112),5,0,N'-'),8,0,N'-')+N'' as times,max(cb) as cb from Table_1 where times > '2013-07-01' and times < '2013-08-01' group by Rooms,times
) as t5 group by rooms,times
2. 时间序列与房间的列表，这样规定的时间内所有的数据都为0
select *,0 as cb from(
select distinct rooms from Table_1) as a1,(
select STUFF(STUFF(CONVERT(char(8),DATEADD(DAY,id-1,@begindays),112),5,0,N'-'),8,0,N'-')+N'' as times from @b) as a2
3. 再和时间序列并在一起（这样每一天的数据都有了）
declare @begindays datetime
set @begindays = '2013-07-01'
DECLARE @b TABLE(id INT IDENTITY,a INT DEFAULT 0);
INSERT @b SELECT TOP 100 0 FROM syscolumns s
select rooms,times,MAX(cb) as cb from(
select *,0 as cb from(
select distinct rooms from Table_1) as a1,(
select STUFF(STUFF(CONVERT(char(8),DATEADD(DAY,id-1,@begindays),112),5,0,N'-'),8,0,N'-')+N'' as times from @b) as a2
union
select rooms,times,MAX(cb) as cb from(
select Rooms,STUFF(STUFF(CONVERT(char(8),MAX(times),112),5,0,N'-'),8,0,N'-')+N'' as times,max(cb) as cb from Table_1 where times > '2013-07-01' and times < '2013-08-01' group by Rooms,times
) as t5 group by rooms,times
) as t6 group by rooms,times
4.进行行列变换
declare @begindays datetime
set @begindays = '2013-07-01'
declare @enddays datetime
set @enddays = '2013-08-01'
declare @days int;
set @days = DATEDIFF(day,@begindays,@enddays);
declare @rooms varchar(50);
set @rooms = 'A4-09-01';
declare @sql nvarchar(max);
set @sql = 'DECLARE @b TABLE(id INT IDENTITY,a INT DEFAULT 0); ';
set @sql = @sql +'INSERT @b SELECT TOP 100 0 FROM syscolumns s ;';
set @sql = @sql+ ' declare @begindays datetime;';
set @sql = @sql+ ' set @begindays = '''+STUFF(STUFF(CONVERT(char(8),@begindays,112),5,0,N'-'),8,0,N'-')+'''';
set @sql = @sql+ ' declare @enddays datetime;';
set @sql = @sql+ ' set @enddays = '''+STUFF(STUFF(CONVERT(char(8),@enddays,112),5,0,N'-'),8,0,N'-')+'''';
set @sql = @sql+ ' DECLARE @days int;';
set @sql = @sql+ ' set @days = DATEDIFF(day,@begindays,@enddays)';
set @sql = @sql+ ' declare @rooms varchar(50);';
set @sql = @sql+ ' set @rooms = '''+@rooms+'''';
set @sql= @sql +' select Rooms ';
select @sql = @sql +' ,max(case when times = STUFF(STUFF(CONVERT(char(8),DATEADD(DAY,'''+cast (number as varchar(3))+'''-0,@begindays),112),5,0,''-''),8,0,''-'') then cb else 0 end) as '''+STUFF(STUFF(CONVERT(char(8),DATEADD(DAY, number ,@begindays),112),5,0,'-'),8,0,'-') +''''
from master..spt_values where [type] = 'p' and number <@days
set @sql = @sql + 'from (
select rooms,times,MAX(cb) as cb from(
select rooms,times,0 as cb from(
select distinct rooms from Table_1) as a1,(
select STUFF(STUFF(CONVERT(char(8),DATEADD(DAY,id-1,@begindays),112),5,0,''-''),8,0,''-'') as times from @b) as a2
union
select rooms,times,MAX(cb) as cb from(
select Rooms,STUFF(STUFF(CONVERT(char(8),MAX(times),112),5,0,''-''),8,0,''-'') as times,max(cb) as cb from Table_1 where times > @begindays and times < @enddays group by Rooms,times
) as t5 group by rooms,times
) as t6 group by rooms,times
) as tt1 group by Rooms'
print (@sql)
print (len(@sql))
exec(@sql)
这样就行成了最后的结查。