首页 新闻 搜索 专区 学院

数据库问题 求具体语句 求具体语句 求具体语句

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!!!!
拜托大侠!!!!!!

ttym88m的主页 ttym88m | 初学一级 | 园豆:4
提问于:2012-05-23 20:34
< >
分享
所有回答(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

$克 | 园豆:217 (菜鸟二级) | 2012-05-24 09:28
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
webaspx | 园豆:1973 (小虾三级) | 2012-05-24 11:12
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册