34,590
社区成员
发帖
与我相关
我的任务
分享
create table su
(姓名 varchar(10),时间 datetime)
insert into su
select '张三', '2013-08-01 08:20:29' union all
select '张三', '2013-08-01 18:20:29' union all
select '李四', '2013-08-01 08:20:29' union all
select '李四', '2013-08-01 18:20:29'
select 姓名,[上午],[下午]
from
(select 姓名,时间,case when datepart(hh,时间)<12 then '上午' else '下午' end 'w'
from su) t
pivot(max(时间) for w in([上午],[下午])) p
order by 姓名 desc
/*
姓名 上午 下午
---------- ----------------------- -----------------------
张三 2013-08-01 08:20:29 2013-08-01 18:20:29
李四 2013-08-01 08:20:29 2013-08-01 18:20:29
(2 row(s) affected)
*/
create table #tb(姓名 varchar(10),时间 datetime)
insert into #tb
SELECT '张三','2013-8-1 8:20:29' UNION ALL
SELECT '张三','2013-8-1 18:20:29' UNION ALL
SELECT '李四 ','2013-8-1 8:20:29' UNION ALL
SELECT '李四 ','2013-8-1 18:20:29'
select 姓名,convert(varchar(10),时间,120) as 日期,MIN(t1) as 上午,MIN(t2) as 下午
from
(
select 姓名,时间,case when rn=1 then 时间 else null end t1,case when rn=2 then 时间 else null end t2
from (select *,rn=ROW_NUMBER() OVER(partition by 姓名 ORDER BY 时间) from #tb)t
)a
group by 姓名,convert(varchar(10),时间,120)
/*
姓名 日期 上午 下午
李四 2013-08-01 2013-08-01 08:20:29.000 2013-08-01 18:20:29.000
张三 2013-08-01 2013-08-01 08:20:29.000 2013-08-01 18:20:29.000
*/
select 姓名,convert(varchar(10),时间,120) as 日期,MIN(t1) as 上午,MIN(t2) as 下午
from
(
select 姓名,case when rn=1 then 时间 else null end t1,case when rn=2 then 时间 else null end t2
from (select *,rn=ROW_NUMBER() OVER(ORDER BY 时间))t
)a
group by 姓名,convert(varchar(10),时间,120)
WITH a1 (NAME,TIME) AS
(
SELECT '张三','2013-8-1 8:20:29' UNION ALL
SELECT '张三','2013-8-1 18:20:29' UNION ALL
SELECT '李四 ','2013-8-1 8:20:29' UNION ALL
SELECT '李四 ','2013-8-1 18:20:29'
)
SELECT a.name,a.TIME,b.TIME
from
(SELECT * FROM a1 WHERE DATEPART(hh,time)<=11) a
INNER JOIN
(SELECT * FROM a1 WHERE DATEPART(hh,time)>11) b
ON a.name=b.name