数据库问题 求具体语句 求具体语句 求具体语句
0
悬赏园豆：80
[待解决问题]
各位大侠：
如何将表
2012-5-22 21:09:00 23 YD78FC 代景平
2012-5-22 21:09:08 89 YD78FC 代景平
2012-5-22 21:09:16 567 YD78FC 代景平
2012-5-22 21:19:16 567 YD78FC 张三
...................................
.....................................
转换成
2012-5-22 21:09:00 2012-5-22 21:09:08 2012-5-22 21:09:16 2012-5-22 21:19:16 ....
23 89 567 567 ....
YD78FC YD78FC YD78FC YD78FC ....
代景平 代景平 代景平 张三 ....
谢谢大侠！
只有YD78FC唯一，
sql 2000!!!!
拜托大侠！！！！！！
所有回答(2)
0
if exists(select 1 from sysobjects where name='tb' and xtype='u')
drop table tb
go
create table tb(
a datetime,
b int,
c varchar(20),
d varchar(20)
)
insert into tb values('2012-5-22 21:09:00',23, 'YD78FC', '代景平')
insert into tb values('2012-5-22 21:09:08', 89 ,'YD78FC', '代景平')
insert into tb values('2012-5-22 21:09:16', 567 ,'YD78FC', '代景平')
insert into tb values('2012-5-22 21:19:16', 567 ,'YD78FC', '张三')
--select * from tb
declare @a varchar(8000)='',@b varchar(8000)='',@c varchar(8000)='',@d varchar(8000)=''
select @a=@a+cast(a as varchar)+' ' ,@b=@b+cast(b as varchar)+' ',@c=@c+c+' ',@d=@d+d+' ' from tb
select @a as R union all
select @b union all
select @c union all
select @d
0
你是需要转成每个时间都是一列呢？还是弄成一列三行呢？
要是弄成一列三行的话楼上是正确的，要是转成每个时间都是一列的话就搜索下SQL2000的行转列就行了。
SQL2000行转列比较麻烦，SQL2005及以上就容易多了
create table #Temp( Dt datetime, Code varchar(10), Num varchar(10), Name varchar(20) ) drop table #temp select * from #Temp insert into #Temp select getdate(),'YD78FC',1,'张三' union all select dateAdd(minute,5,getdate()),'YD78FC',2,'李四' union all select dateAdd(minute,10,getdate()),'YD78FC',3,'王二' union all select dateAdd(minute,15,getdate()),'YD78FC',4,'麻子' Select max(case when CONVERT(varchar(100), dt, 21)='2012-05-24 11:07:46.520' then Name end) As '2012-05-24 11:07:46.520' , max(case when CONVERT(varchar(100), dt, 21)='2012-05-24 11:12:46.520' then Name end) As '2012-05-24 11:12:46.520' , max(case when CONVERT(varchar(100), dt, 21)='2012-05-24 11:17:46.520' then Name end) As '2012-05-24 11:17:46.520', max(case when CONVERT(varchar(100), dt, 21)='2012-05-24 11:22:46.520' then Name end) As '2012-05-24 11:22:46.520' From #temp Group By Code union all Select max(case when CONVERT(varchar(100), dt, 21)='2012-05-24 11:07:46.520' then Num end) As '2012-05-24 11:07:46.520' , max(case when CONVERT(varchar(100), dt, 21)='2012-05-24 11:12:46.520' then Num end) As '2012-05-24 11:12:46.520' , max(case when CONVERT(varchar(100), dt, 21)='2012-05-24 11:17:46.520' then Num end) As '2012-05-24 11:17:46.520', max(case when CONVERT(varchar(100), dt, 21)='2012-05-24 11:22:46.520' then Num end) As '2012-05-24 11:22:46.520' From #temp Group By Code
