自动增加长列的使用加行列转换

客户需求这样的结果，还要求可以按要求时间（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)

这样就行成了最后的结查。