日期:2014-05-18  浏览次数:20504 次

求助:表记录转置为多查询结果
表结构如下:
表A:
name string 
date datetime
07:00 bool
08:00 bool
。。。


表数据:
name date 07:00 08:00 09:00 10:00 11:00 12:00
gm 2012-06-20 1 0 1 0 1 0
gm 2012-06-11 1 0 1 0 0 0


求教:
按照每个time时间的bool值,只取true,如何让查询结果转变成为:

name date time
gm 2012-06-20 07:00
gm 2012-06-20 09:00
gm 2012-06-20 11:00
gm 2012-06-11 07:00
gm 2012-06-11 09:00


这样做是否可行?请教大家。









------解决方案--------------------
SQL code

--> 测试数据:[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
*/