27,579
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(8000)
set @sql = 'select PersonID,YM as 日期 '
select @sql = @sql + ' ,max(case cast(d as varchar) when '''+cast(d as varchar)+''' then right(CONVERT(varchar, brushDate, 120 ),9) else null end)[时间'+cast(isnull(d,0) as varchar)+']'
from (select distinct d from (select a.PersonID,left(CONVERT(varchar, a.brushDate, 120 ),10) as YM,a.brushDate,(select count(1)+1 from #temp b where b.brushDate >=
left(CONVERT(varchar, a.brushDate, 120 ),10) and b.brushDate < a.brushDate and b.PersonID=a.PersonID)
as d from #temp a ) s) as b
set @sql = @sql + 'from (select a.PersonID,left(CONVERT(varchar, a.brushDate, 120 ),10) as YM,a.brushDate,(select count(1)+1 from #temp b where b.brushDate >=
left(CONVERT(varchar, a.brushDate, 120 ),10) and b.brushDate < a.brushDate and b.PersonID=a.PersonID)
as d from #temp a ) as m group by PersonID,YM'
exec(@sql)
--SQL
select
PersonID,
日期 = [date],
时间One = convert(varchar(8),[1],108),
时间Two = convert(varchar(8),[2],108),
时间Three = convert(varchar(8),[3],108),
时间Four = convert(varchar(8),[4],108),
时间Five = convert(varchar(8),[5],108),
时间Six = convert(varchar(8),[6],108),
时间Seven= convert(varchar(8),[7],108)
from
(
select
rowno=ROW_NUMBER() over(partition by convert(varchar(10), BrushDate, 120) order by BrushDate),
[date] = convert(varchar(10), BrushDate, 120), *
from #temp
) a
pivot
(max(BrushDate) for rowno in([1],[2],[3],[4],[5],[6],[7])) b
/*
PersonID 日期 时间One 时间Two 时间Three 时间Four 时间Five 时间Six 时间Seven
1052 2011-03-01 07:34:30 07:44:16 12:01:48 12:43:46 17:00:49 17:24:08 20:32:03
1052 2011-03-02 07:48:09 12:01:51 13:27:39 17:01:30 17:24:28 20:05:45 NULL
*/
create table #temp(PersonID int, BrushDate datetime)
insert #temp
select 1052, '2011-03-01 07:34:30.000' union all
select 1052, '2011-03-01 07:44:16.000' union all
select 1052, '2011-03-01 12:01:48.000' union all
select 1052, '2011-03-01 12:43:46.000' union all
select 1052, '2011-03-01 17:00:49.000' union all
select 1052, '2011-03-01 17:24:08.000' union all
select 1052, '2011-03-01 20:32:03.000' union all
select 1052, '2011-03-02 07:48:09.000' union all
select 1052, '2011-03-02 12:01:51.000' union all
select 1052, '2011-03-02 13:27:39.000' union all
select 1052, '2011-03-02 17:01:30.000' union all
select 1052, '2011-03-02 17:24:28.000' union all
select 1052, '2011-03-02 20:05:45.000'
go
--SQL
select
PersonID,
日期 = [date],
时间One = [1],
时间Two = [2],
时间Three = [3],
时间Four = [4],
时间Five = [5],
时间Six = [6],
时间Seven= [7]
from
(
select
rowno=ROW_NUMBER() over(partition by convert(varchar(10), BrushDate, 120) order by BrushDate),
[date] = convert(varchar(10), BrushDate, 120), *
from #temp
) a
pivot
(max(BrushDate) for rowno in([1],[2],[3],[4],[5],[6],[7])) b
/*
PersonID 日期 时间One 时间Two 时间Three 时间Four 时间Five 时间Six 时间Seven
1052 2011-03-01 2011-03-01 07:34:30.000 2011-03-01 07:44:16.000 2011-03-01 12:01:48.000 2011-03-01 12:43:46.000 2011-03-01 17:00:49.000 2011-03-01 17:24:08.000 2011-03-01 20:32:03.000
1052 2011-03-02 2011-03-02 07:48:09.000 2011-03-02 12:01:51.000 2011-03-02 13:27:39.000 2011-03-02 17:01:30.000 2011-03-02 17:24:28.000 2011-03-02 20:05:45.000 NULL
*/