สอบถามเกี่ยวกับการแปลงวันที่และเวลาและการ กรุ๊ปให้อยู่ในแนวนอนครับ

SELECT 
	USERID,CAST(DATEPART(dd, CHECKTIME) AS varchar(2)) + '/' + CAST(DATEPART(mm, CHECKTIME) AS varchar(2)) + '/' + CAST(DATEPART(yyyy, CHECKTIME) AS varchar(4)) AS EmpDate,
	
	(CHECKTIME) AS [time1],
	(CHECKTIME) AS [time2],
	(CHECKTIME) AS [time3],
	(CHECKTIME) AS [time4]

FROM CHECKINOUT

WHERE USERID = '1' 

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(date +'_'+Logname) 
                    from
                    (
                      select checktime,
                        convert(char(10),checktime, 101) date, 
                        LogName
                      from CHECKINOUT
                      cross apply
                      (
                        select 'In' LogName
                        union all
                        select 'Out' 
                      ) l
                    ) s   
                    group by convert(char(10), checktime, 112), date, Logname
                    order by convert(char(10), checktime, 112)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
    = 'select userid, name, badgenumber, '+@cols+'
       from
       (
         select userid, name, badgenumber,
          convert(char(8), doortime, 108) DoorTime,
          date + ''_''+ col col_names
         from
         (
            select p.userid, p.name, p.badgenumber,
              convert(char(10),d.checktime, 101) date,
              min(d.checktime) [In],
              max(d.checktime) [Out]

            from USERINFO p
            left join CHECKINOUT d
            on p.userid = d.userid
            group by p.userid, p.name, p.badgenumber,
            convert(char(10),d.checktime, 101)
         )src
         unpivot
         (
           doortime
           for col in ([In], [Out])
         ) unpiv
       ) p
       pivot
       (
          max(doortime)
          for col_names in('+@cols+')
       ) piv'

execute(@query)