How to Construct a Single Row from a Multi Row Subset in SQlLSERVER

Question

I have a situation where employees have time-in , and time-out but they are saved into a single column with a type [in =1 ,out = 2], I need to fetch a single employees time (in-out) in a single row separating with Time-In, Time-Out headers.

Here is the example of table that I have ,

CREATE TABLE #Employee
(
empid int,
name varchar(20),
age int
)
CREATE TABLE #TIMEINOUT
(
    id int,
    empid int,
    timeinout datetime,
    [type] tinyint
)

INSERT INTO #Employee values(1,'Benny',35)
INSERT INTO #Employee values(2,'Algo',32)


INSERT INTO #TIMEINOUT VALUES(1,1,'2017-03-08 06:00:00 AM',1) -- (Type 1 = IN , 2 = Out)
INSERT INTO #TIMEINOUT VALUES(2,1,'2017-03-08 05:00:00 PM',2) -- (Type 1 = IN , 2 = Out)
INSERT INTO #TIMEINOUT VALUES(3,2,'2017-03-08 07:00:00 AM',1) -- (Type 1 = IN , 2 = Out)
INSERT INTO #TIMEINOUT VALUES(4,2,'2017-03-08 09:00:00 PM',2) -- (Type 1 = IN , 2 = Out)



SELECT * FROM #Employee INNER JOIN #TIMEINOUT ON #Employee.empid = #TIMEINOUT.empid

Select #Employee.empid,#Employee.name,#Employee.age,GETDATE() as TimeIN,GETDATE() as TimeOUT from #Employee

DROP TABLE #Employee
DROP TABLE #TIMEINOUT

Can anybody help to simplify the query?

Answer 1

This is how I did,

SELECT  #Employee.empid ,
    #Employee.name ,
    #TIMEINOUT.timeinout [TimeIN],
    X.timeinout [TimeOUT]
FROM    #Employee
    INNER JOIN #TIMEINOUT ON #TIMEINOUT.empid = #Employee.empid
    LEFT JOIN ( SELECT  #TIMEINOUT.empid ,
                        #TIMEINOUT.timeinout
                FROM    #TIMEINOUT
                WHERE   type = 2
              ) X ON X.empid = #Employee.empid
                     AND CONVERT(VARCHAR(10), #TIMEINOUT.timeinout, 111)   = CONVERT(VARCHAR(10), X.timeinout, 111)
WHERE   #TIMEINOUT.type = 1