求救,数据库带时间的行转列问题?
数据库有表
表
ID   重量     时间       测量次数  
11     24         2007-10-4   12:22:00         1
11     23         2007-10-4   12:26:00         2
11     23         2007-10-4   12:28:00         3
12     12         2007-10-5   12:22:00         1
12     14         2007-10-5   12:24:00         2
12     13         2007-10-5   12:28:00         3
转化后到视图
模式为
ID   重量1   时间1     重量2       时间2       重量三     时间3




------解决方案--------------------
create table tb(ID int,重量 int,时间 datetime,测量次数 int)
insert into tb values(11, 24, '2007-10-4 12:22:00 ', 1)
insert into tb values(11, 23, '2007-10-4 12:26:00 ', 2)
insert into tb values(11, 23, '2007-10-4 12:28:00 ', 3)
insert into tb values(12, 12, '2007-10-5 12:22:00 ', 1)
insert into tb values(12, 14 , '2007-10-5 12:24:00 ', 2)
insert into tb values(12, 13, '2007-10-5 12:28:00 ', 3)
go

declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then 重量 end) [重量 ' + cast(px as varchar) + '] '
+ ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then 时间 end) [时间 ' + cast(px as varchar) + '] '
from (select distinct px from (select px=(select count(1) from tb where id=a.id and 时间 <a.时间)+1 , * from tb a) t) as m
set @sql = @sql + ' from (select px=(select count(1) from tb where id=a.id and 时间 <a.时间)+1 , * from tb a) t group by id '
exec(@sql)

drop table tb

/*
id 重量1 时间1 重量2 时间2 重量3 时间3
----------- ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------
11 24 2007-10-04 12:22:00.000 23 2007-10-04 12:26:00.000 23 2007-10-04 12:28:00.000
12 12 2007-10-05 12:22:00.000 14 2007-10-05 12:24:00.000 13 2007-10-05 12:28:00.000
*/
------解决方案--------------------
create table tt( id int ,weight int ,createtime datetime,test_num int)
insert into tt select 11 , 24, '2007-10-4 12:22:00 ', 1
insert into tt select 11 , 23, '2007-10-4 12:26:00 ', 2
insert into tt select 11 , 23, '2007-10-4 12:28:00 ', 3
insert into tt select 12 , 24, '2007-10-5 12:22:00 ', 1
insert into tt select 12 , 23, '2007-10-5 12:26:00 ', 2
insert into tt select 12 , 23, '2007-10-5 12:28:00 ', 3

declare @sql varchar(1000)
set @sql= 'select id '
select @sql=@sql+ ',max(case when test_num= ' ' '+ltrim(test_num)+ ' ' '