Return to Answer

This is an example of pivoting:

select worker_name, max(_in) as _in, max(_out) as _out 
from (
    select worker_name
         , case when in_out = 'IN' then log_time end as _in
         , case when in_out = 'OUT' then log_time end as _out 
    from shift
) as t 
group by worker_name;

The idea is to map log_time to _in when in_out = 'IN', ditto for _out. In the outer select an aggregate function is used to filter out nulls. Try running the inner select first to get an idea of what is happening.

In general, presentation is better handled by the application layer, so I would suggest reading the table as is, and do the presentation in the application.

This is an example of pivoting:

select worker_name, max(_in) as _in, max(_out) as _out 
from (
    select worker_name
         , case when in_out = 'IN' then log_time end as _in
         , case when in_out = 'OUT' then log_time end as _out 
    from shift
) as t 
group by worker_name;

The idea is to map log_time to _in when in_out = 'IN', ditto for _out. In the outer select an aggregate function is used to filter out nulls. Try running the inner select first to get an idea of what is happening.

In general, the presentation of data is better handled by the application layer. So I would suggest reading the table as is, and let the presentation of the data be handled by the application.

This is an example of pivoting:

select worker_name, max(_in) as _in, max(_out) as _out 
from (
    select worker_name
         , case when in_out = 'IN' then log_time end as _in
         , case when in_out = 'OUT' then log_time end as _out 
    from shift
) as t 
group by worker_name;

The idea is to map log_time to _in when in_out = 'IN', dito for _out. In the outer select an aggregate function is used to filter out nulls. Try running the inner select first to get an idea of what is happening.

In general, presentation is better handled by the application layer, so I would suggest reading the table as is, and do the presentation in the application.

This is an example of pivoting:

select worker_name, max(_in) as _in, max(_out) as _out 
from (
    select worker_name
         , case when in_out = 'IN' then log_time end as _in
         , case when in_out = 'OUT' then log_time end as _out 
    from shift
) as t 
group by worker_name;

The idea is to map log_time to _in when in_out = 'IN', ditto for _out. In the outer select an aggregate function is used to filter out nulls. Try running the inner select first to get an idea of what is happening.

In general, presentation is better handled by the application layer, so I would suggest reading the table as is, and do the presentation in the application.

This is an example of pivoting:

select worker_name, max(_in) as _in, max(_out) as _out 
from (
    select worker_name
         , case when in_out = 'IN' then log_time end as _in
         , case when in_out = 'OUT' then log_time end as _out 
    from shift
) as t 
group by worker_name;

The idea is to map log_time to _in when in_out = 'IN', dito for _out. In the outer select an aggregate function is used to filter out nulls. Try running the inner select first to get an idea of what is happening.

In general, presentation is better handled by the application layer, so I would suggest reading the table as is, and do the presentation in the application.

This is an example of pivoting:

select worker_name, max(_in) as _in, max(_out) as _out 
from (
    select worker_name
         , case when in_out = 'IN' then log_time end as _in
         , case when in_out = 'OUT' then log_time end as _out 
    from shift
) as t 
group by worker_name;

The idea is to map log_time to _in when in_out = 'IN', dito for _out. In the outer select an aggregate function is used to filter out nulls. Try running the inner select first to get an idea of what is happening.

In general, presentation is better handled by the application layer, so I would suggest reading the table as is, and do the presentation in the application.