locked
how to show two select by two column RRS feed

  • 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

    Monday, July 20, 2020 12:36 PM

All replies

  • 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

    Tuesday, July 21, 2020 3:30 AM