SqlServer 日期的行转列

dzcgo

已于 2023-02-06 17:42:53 修改

阅读量960

点赞数

文章标签： sqlserver sql 数据库

于 2023-02-06 16:57:45 首次发布

本文链接：https://blog.csdn.net/dzcgo/article/details/128904388

版权

思路：

网上查到的行转列都是用聚合函数，像成绩这种。因此我把日期转成日期时间戳，再进行行转列。

/*将时间转换成时间戳*/
(cast((DATEDIFF(S,'1970-01-01 08:00:00', 要转换的时间) - 8 * 3600) AS bigint)) as sjc
/*再根据时间判断 上午和下午*/
(CASE WHEN (datepart(hour,要转换的时间)>=0 and datepart(hour, 要转换的时间)<12) THEN '上午' ELSE '下午' END) as sxw
/*将时间戳转成时间，因为最后我需要的是时分秒所以使用CONVERT再进行转换*/
CONVERT(VARCHAR(50),DATEADD(S,时间戳,'1970-01-01 08:00:00'),108) as qd_sj

最后附上整个sql语句和图片

	WITH	
	kq as (select samt.doc_creator_id ,samt.fd_id ,(case when samt.fd_status = 0 then '缺卡' 
	 when samt.fd_status = 1 then '正常'
     when samt.fd_status= 2 then '迟到'
     when samt.fd_status= 3 then '早退'
     when samt.fd_status= 4 then '出差'
     when samt.fd_status= 5 then '请假' 
      end) as status,fd_status ,doc_create_time ,(case when (datepart(hour,doc_create_time)>=0 and datepart(hour, doc_create_time)<12) THEN '签到状态' ELSE '签退状态' END) as zhuangtai,soe2.fd_name as jigou,soe3.fd_name as bumen,soe.fd_name as name,CONVERT(varchar(100), doc_create_time, 23) dktime,samt.fd_location as xdaddress, 
	(cast((DATEDIFF(S,'1970-01-01 08:00:00', doc_create_time)) AS bigint)) as sjc,(CASE WHEN (datepart(hour,doc_create_time)>=0 and datepart(hour, doc_create_time)<12) THEN '上午' WHEN (datepart(hour,doc_create_time)<=24 and datepart(hour, doc_create_time)>12) then '下午' else null END) as sxw
			from sys_attend_main_two samt join sys_org_element soe on samt.doc_creator_id  = soe.fd_id
			join sys_org_element soe2 on soe2.fd_id = soe.fd_parentorgid join sys_org_element soe3 on soe3.fd_id=soe.fd_parentid)
	select samt.*,row_number () over(order by dktime asc) pageNumber 
	from (select k2.dktime,k2.jigou,k2.name,k2.bumen,(case when qdzt = 0 then '缺卡'
	 when qdzt = 1 then '正常'
     when qdzt = 2 then '迟到'
     when qdzt = 3 then '早退'
     when qdzt = 4 then '出差'
     when qdzt = 5 then '请假' end) qd_zt,(case when qtzt = 0 then '缺卡'
	 when qtzt = 1 then '正常'
     when qtzt = 2 then '迟到'
     when qtzt = 3 then '早退'
     when qtzt = 4 then '出差'
     when qtzt = 5 then '请假'end) qt_zt,CONVERT(VARCHAR(50),DATEADD(S,qdsj,'1970-01-01 08:00:00'),108) as qd_sj,
    CONVERT(VARCHAR(50),DATEADD(S,qtsj,'1970-01-01 08:00:00'),108) as qt_sj
     from (
			(select dktime,jigou,name,bumen,max(签到状态)as qdzt,max(签退状态) as qtzt  from kq 
			pivot(sum(fd_status) for zhuangtai IN(签到状态,签退状态)) x
			group by dktime,name,bumen,jigou ) k2
			left JOIN 
			(select dktime,jigou,name,bumen,max(case when sxw='上午' then sjc end) as qdsj,
     				max(case  when sxw='下午' then sjc end) qtsj from kq group by dktime,jigou,name,bumen) k1 on k2.dktime=k1.dktime and k2.name=k1.name
		     
			)) samt