求救,数据库带时间的行转列问题?
数据库有表
表
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)+ ' ' ' then weight end) as 重量 '+ltrim(test_num)+ ',
max(case when test_num= ' ' '+ltrim(test_num)+ ' ' ' then createtime end) as 测试时间 '+ltrim(test_num)
from (select distinct test_num from tt) a
set @sql= @sql+ ' from tt group by id '
exec(@sql)
[解决办法]
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 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)
select * from tb
declare @s varchar(8000)
select @s= 'select ID '
select @s=@s+ ',[重量 '+cast(测量次数 as varchar)+ ']=sum(case 测量次数 when '+cast(测量次数 as varchar)+ ' then 重量 else 0 end), '
+ '[时间 '+cast(测量次数 as varchar)+ ']=max(case 测量次数 when '+cast(测量次数 as varchar)+ ' then 时间 else 0 end) '
from tb group by 测量次数
select @s=@s+ ' from tb group by ID '
print(@s)
exec(@s)
结果:(是对的..)
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
