declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',[' + convert(varchar,date_time,120) + '] = max(case date_time when ''' + cast(date_time as varchar) + ''' then duration else 0 end)'
from #test group by date_time
set @sql = 'select number' + @sql + ' from #test group by number'
print @sql
EXEC(@sql)列太多。时间列一共是250列,每一列占用字符98个,这样一个@sql最多40个时间列。
但是多定义变量得话,语句应该怎么改呢?
好像这样行不通了吧?
请指点!
set @sql = ''
select @sql = @sql + ',[' + convert(varchar,date_time,120) + '] = max(case date_time when ''' + cast(date_time as varchar) + ''' then duration else 0 end)'
from #test group by date_time
set @sql = 'select number' + @sql + ' from #test group by number'
print @sql
EXEC(@sql)列太多。时间列一共是250列,每一列占用字符98个,这样一个@sql最多40个时间列。
但是多定义变量得话,语句应该怎么改呢?
好像这样行不通了吧?
请指点!
drop table #test
if object_id('tempdb..#tmp') is not null
drop table #tmp
GO
----创建测试数据
create table #test(number int, date_time datetime, duration decimal(10,1))
insert #test
select 2, '2006-09-05 12:00:00', 32.5 union all
select 2, '2006-09-05 12:30:00', 32.2 union all
select 2, '2006-09-05 13:00:00', 52.7 union all
select 3, '2006-09-05 12:00:00', 41.5 union all
select 3, '2006-09-05 12:30:00', 10.5 union all
select 4, '2006-09-05 13:00:00', 22.3----生成交叉汇总表列名临时表,用于更加列名ID而分段创建SQL字符串
select id = identity(int,1,1),date_time into #tmp from
(select date_time from #test group by date_time) as a order by date_time
----动态交叉汇总
declare @sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)
set @sql1 = ''
set @sql2 = ''
set @sql3 = ''
set @sql4 = ''
select @sql1 = @sql1 + ',[' + convert(varchar(19),date_time,120) + '] = max(case date_time when ''' + cast(date_time as varchar) + ''' then duration else 0 end)'
from #tmp where id between 1 and 70
select @sql2 = @sql2 + ',[' + convert(varchar(19),date_time,120) + '] = max(case date_time when ''' + cast(date_time as varchar) + ''' then duration else 0 end)'
from #tmp where id between 71 and 140
select @sql3 = @sql3 + ',[' + convert(varchar(19),date_time,120) + '] = max(case date_time when ''' + cast(date_time as varchar) + ''' then duration else 0 end)'
from #tmp where id between 141 and 210
select @sql4 = @sql4 + ',[' + convert(varchar(19),date_time,120) + '] = max(case date_time when ''' + cast(date_time as varchar) + ''' then duration else 0 end)'
from #tmp where id between 211 and 280
--查看SQL
print 'select number' + @sql1 + @sql2 + @sql3 + @sql4 + ' from #test group by number'
--执行SQL
EXEC('select number' + @sql1 + @sql2 + @sql3 + @sql4 + ' from #test group by number')----清除测试环境
drop table #test,#tmp/*结果
2 32.5 32.2 52.7
3 41.5 10.5 .0
4 .0 .0 22.3
*/
的结果了吗?
打印的SQL字符串是正确的,那么就会正确执行.
执行时必须使用这样的语法格式:
EXEC('select number' + @sql1 + @sql2 + @sql3 + @sql4 + ' from #test group by number')
而不是
declare @sql varchar(8000)
set @sql = 'select number' + @sql1 + @sql2 + @sql3 + @sql4 + ' from #test group by number'
EXEC(@sql)
后来执行了一下,没有报错。好像成功了。
谢谢hellowork(一两清风),太感谢了。明天测试一下,看看效率提高多少。
上面的代码只是查询,而不会修改原表,所以可以放心地执行试一下.