日期:2014-05-18 浏览次数:20504 次
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [name] varchar(2), [date] datetime, [07:00] int, [08:00] int, [09:00] int, [10:00] int, [11:00] int, [12:00] int ) go insert [test] select 'gm','2012-06-20',1,0,1,0,1,0 union all select 'gm','2012-06-11',1,0,1,0,0,0 go declare @s nvarchar(4000) select @s=isnull(@s+' union all ','')+'select [name],[date],[time] ='+quotename(Name,'''') +',[value]='+quotename(Name)+' from test' from syscolumns where ID=object_id('test') and Name not in('name','date') order by Colid exec('select name,convert(varchar(10),date,120) as date,time from( select * from ('+@s+')t )m where value=1 order by [name],[date]') /* name date time -------------------------------------- gm 2012-06-11 07:00 gm 2012-06-11 09:00 gm 2012-06-20 11:00 gm 2012-06-20 09:00 gm 2012-06-20 07:00 */