how to show two select by two column

Question

User-1634604574 posted

i want to show these two select by one table result with two column how can i do it?

 (select time,Employee from Employee_Checkin where log_type='in' order by time asc)as time_in,

(select time,Employee from Employee_Checkin where log_type='out' order by time asc)as time_out

Answer 1

User-939850651 posted

Hi zhyanadil.it,

Please refer to the following code:

create table Employee_Checkin(
[id] int identity(1,1),
[time] time,
[Employee] varchar(50),
[log_type] varchar(50)
)

insert into Employee_Checkin values ('9:51','Name1','in'),('13:24','Name2','out'),('10:43','Name3','in'),
				    ('11:22','Name4','out'),('5:26','Name5','in'),('7:14','Name6','out'),
				    ('12:51','Name7','out'),('15:39','Name8','in'),('22:32','Name9','in');

WITH ta AS (
    SELECT convert(varchar,time)+' '+Employee AS time_in, ROW_NUMBER() OVER (ORDER BY time asc) AS RN1
    FROM Employee_Checkin where log_type='in'),
tb AS (
    SELECT convert(varchar,time)+' '+Employee AS time_out, ROW_NUMBER() OVER (ORDER BY time asc) AS RN2
    FROM Employee_Checkin where log_type='out')

select ta.time_in,tb.time_out from ta left join tb on ta.RN1=tb.RN2

Result:

Hope this can help you.

Best regards,

Xudong Peng