LEFT JOIN Multiple rows from the same table | SQL SERVER 2012

I have a problem with this query. I don't know how to approach this since the data is within the same column and can only be separated with the labesl I, and O. how will I approach this? Is this possible via multiple LEFT JOIN?

SELECT
CHECKINOUT.userid as chkuserid, USERINFO.userid as usruserid, name, 
BADGENUMBER as badge,
LEFT(CONVERT(varchar, CHECKTIME, 23),11) as logindate,checktype,
Format(cast(RIGHT(CHECKTIME,12) as datetime),'HH:mm:ss') AS timestamp
from CHECKINOUT,USERINFO
where CHECKINOUT.userid=USERINFO.userid
and BADGENUMBER = '1693'

This is what the query looks like.

enter image description here

What I want to achieve is for it to look like this.

enter image description here

May I ask for assistance regarding this matter?

1 answer

  • answered 2019-08-13 03:18 ϻᴇᴛᴀʟ

    how about this.

    select t1.*, t2.checktype, t2.timestamp from 
        (SELECT
        CHECKINOUT.userid as chkuserid, USERINFO.userid as usruserid, name, 
        BADGENUMBER as badge,
        LEFT(CONVERT(varchar, CHECKTIME, 23),11) as logindate,checktype,
        Format(cast(RIGHT(CHECKTIME,12) as datetime),'HH:mm:ss') AS timestamp
        from CHECKINOUT,USERINFO
        where CHECKINOUT.userid=USERINFO.userid
        and BADGENUMBER = '1693' and  checktype = 'I') as t1
    left join
        (select CHECKINOUT.userid as chkuserid, checktype, LEFT(CONVERT(varchar, CHECKTIME, 23),11) as logindate,
            Format(cast(RIGHT(CHECKTIME,12) as datetime),'HH:mm:ss') AS timestamp
        from CHECKINOUT,USERINFO
        where CHECKINOUT.userid=USERINFO.userid
        and BADGENUMBER = '1693' and  checktype = '0')) as t2 on t2.chkuserid = t1.chkuserid
            and t2.logindate = t1.logindate