时间问题!---优快云

怎么把这样的日期列表,转换成下面那样的啊? 日期 时间 2006-06-0108:21:02 2006-06-0112:03:36 2006-06-0112:26:48 2006-06-0117:41:36 2006-06-0208:25:45 2006-06-0212:03:22 2006-06-0212:32:50 2006-06-0217:35:03 2006-06-0308:25:27 2006-06-0312:05:17 2006-06-0312:29:21 2006-06-0317:37:04 2006-06-0408:26:29 2006-06-0412:10:41 2006-06-0413:11:42 2006-06-0417:34:05 日期 时间1 时间2 时间3 时间4 2006-06-01 08:21:02 12:03:36 12:26:48 17:41:36 2006-06-01 .......................................... ............................................. 请高手赐教,多谢

	wgsasd311(自强不息) ( ) 信誉：100	2006-7-12 14:47:45	得分: 3
	--固定时间字段4个，多于4个则换行 create table test(日期 varchar(10),时间 varchar(8)) insert into test select '2006-06-01','08:21:02' union all select '2006-06-01','12:03:36' union all select '2006-06-01','12:26:48' union all select '2006-06-01','17:41:36' union all select '2006-06-02','08:25:45' union all select '2006-06-02','12:03:22' union all select '2006-06-02','01:25:45' union all select '2006-06-02','12:32:50' union all select '2006-06-02','17:35:03' union all select '2006-06-03','08:25:27' union all select '2006-06-03','12:05:17' union all select '2006-06-03','12:29:21' union all select '2006-06-04','08:26:29' union all select '2006-06-04','12:10:41' union all select '2006-06-04','13:11:42' union all select '2006-06-04','17:34:05' go declare @dt varchar(10),@tt varchar(8),@i int,@dt0 varchar(10),@id int,@sql varchar(4000) create table #tb (id int identity,日期 varchar(10),时间1 varchar(8),时间2 varchar(8),时间3 varchar(8), 时间4 varchar(8) ) declare cur cursor for select * from test order by 日期,时间 open cur set @i=0 fetch next from cur into @dt,@tt while @@fetch_status=0 begin set @i=@i+1 if @i%4=1 or @dt<>@dt0 begin insert into #tb(日期,时间1)values(@dt,@tt) SELECT @dt0=@dt,@i=1 end else begin select @id=max(id) from #tb set @sql='update #tb set 时间'+cast(@i as varchar)+'='''+@tt+''' where id='+cast(@id as varchar) exec(@sql) end fetch next from cur into @dt,@tt end select * from #tb deallocate cur go drop table test,#tb

	fcuandy(要学的东西还很多) ( ) 信誉：100	2006-7-12 15:40:45	得分: 17
	CREATE TABLE test(日期 DATETIME, 时间 VARCHAR(20)) INSERT test SELECT '2006-06-01','08:21:02' UNION ALL SELECT '2006-06-01','12:03:36' UNION ALL SELECT '2006-06-01','12:26:48' UNION ALL SELECT '2006-06-01','17:41:36' UNION ALL SELECT '2006-06-02','08:25:45' UNION ALL SELECT '2006-06-02','12:03:22' UNION ALL SELECT '2006-06-02','14:03:22' UNION ALL SELECT '2006-06-02','19:03:22' UNION ALL SELECT '2006-06-02','12:32:50' UNION ALL SELECT '2006-06-02','17:35:03' UNION ALL SELECT '2006-06-03','08:25:27' UNION ALL SELECT '2006-06-03','12:05:17' UNION ALL SELECT '2006-06-03','12:29:21' UNION ALL SELECT '2006-06-03','17:37:04' UNION ALL SELECT '2006-06-04','08:26:29' UNION ALL SELECT '2006-06-04','12:10:41' UNION ALL SELECT '2006-06-04','13:11:42' UNION ALL SELECT '2006-06-04','17:34:05' UNION ALL SELECT '2006-06-04','17:34:15' SELECT a.日期,a.时间,b.cnt,IDENTITY(int) ID INTO Test1 FROM Test a INNER JOIN (SELECT 日期,COUNT(*) cnt FROM test GROUP BY 日期) b ON a.日期=b.日期 DECLARE @MaxFieldNum INT,@i INT SELECT @i=0,@MaxFieldNum=MAX(cnt) FROM Test1 DECLARE @sql VARCHAR(8000) SET @sql='' WHILE @i<@MaxFieldNum SELECT @sql=@sql + ',f' + RTRIM(@i) + '=' + '(SELECT 时间 FROM Test1 b WHERE b.日期=a.日期 AND ' + RTRIM(@i) + '=(SELECT COUNT(1) FROM Test1 c WHERE c.日期=b.日期 AND c.id<b.id))',@i=@i+1 SELECT @sql='SELECT CONVERT(VARCHAR(10),日期,120) 日期' + @sql + ' FROM Test1 a GROUP BY 日期' EXEC(@sql) DROP TABLE Test1 DROP TABLE test /*------结果----------------- 日期f0f1f2f3f4f5f6 2006-06-0108:21:0212:03:3612:26:4817:41:36NULLNULL 2006-06-0208:25:4512:03:2214:03:2219:03:2212:32:5017:35:03 2006-06-0308:25:2712:05:1712:29:2117:37:04NULLNULL 2006-06-0408:26:2912:10:4113:11:4217:34:0517:34:15NULL */

转载于:https://www.cnblogs.com/kentyshang/archive/2006/07/12/448979.html