How to transpose rows into columns in SQL?

Question

Sorry for misleading question but I don't know hot to name my issue.

My db<>fiidle is here.

I have a table with defined as follows:

CREATE TABLE shift (
     worker_name VARCHAR(25), 
     log_time DATETIME, 
     in_out VARCHAR(3)
);

It contains the following data:

INSERT INTO shift VALUES ('User 1', '2018-05-14 07:24:40.000', 'IN');
INSERT INTO shift VALUES ('User 2', '2018-05-14 11:55:29.000', 'IN');
INSERT INTO shift VALUES ('User 3', '2018-05-14 14:03:26.000', 'IN');
INSERT INTO shift VALUES ('User 1', '2018-05-14 14:05:08.000', 'Out');
INSERT INTO shift VALUES ('User 3', '2018-05-14 20:08:30.000', 'Out');
INSERT INTO shift VALUES ('User 2', '2018-05-14 20:08:36.000', 'Out');
INSERT INTO shift VALUES ('User 4', '2018-05-14 20:09:14.000', 'Out');

And as a result I would like to get:

worker_name | IN                  | OUT
:---------- | :------------------ | :------------------
User 1      | 14/05/2018 07:24:40 | 14/05/2018 14:05:08   
User 2      | 14/05/2018 11:55:29 | 14/05/2018 20:08:36   
User 3      | 14/05/2018 14:03:26 | 14/05/2018 20:08:30     
User 4      |      ----------     | 14/05/2018 20:09:14 

How can I achieve that?

Answer 1

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.

Answer 2

This appears to create the desired result in your db<>fiddle.

WITH sIN(Ctr, worker_name, log_time)
AS (
 SELECT ROW_NUMBER()OVER(PARTITION BY worker_name ORDER BY log_time) AS     Ctr, worker_name, log_time
 FROM shift
 WHERE in_out = 'IN'
 ),sOUT(Ctr, worker_name, log_time)
AS (
 SELECT ROW_NUMBER()OVER(PARTITION BY worker_name ORDER BY log_time) AS Ctr, worker_name, log_time
 FROM shift
 WHERE in_out = 'Out'
 )
  SELECT ISNULL(sIN.worker_name, sOUT.worker_name) AS worker_name,
    ISNULL(CONVERT(VARCHAR(30),sIN.log_time,120),'-----') AS [In],
    ISNULL(CONVERT(VARCHAR(30),sOUT.log_time,120),'-----') AS [Out]
 FROM sIN FULL OUTER JOIN sOUT
   ON sIN.worker_name = sOUT.worker_name AND sIN.Ctr = sOUT.Ctr
 ORDER BY 1,2,3

However, there's nothing preventing IN from being after OUT on the resultset. A more thorough solution could solve that, but I leave it as an exercise for the reader.

Answer 3

There is no constraint on that table to stop multiple in or out
Put a PK or unique constraint on worker_name, in_out

select s1.worker_name, sIn.in_out as in, sOut.in_out as out
  from shift sIn
  full outer join shift sOut 
    on sIn.worker_name = sOut.worker_name  
   and sIn.In_out  = 'IN'
   and sOut.in_out = 'Out' 
 order by s1.worker_name, sIn.in_out, sOut.in_out

Answer 4

This should get you started:

    select s1.worker_name, s1.in_out as in, s2.in_out as out
    from shift s1
    left join shift s2 on s1.worker_name = s2.worker_name 
    and cast(s1.log_time as date) = cast(s2.log_time as date)
    where s1.In_out = 'IN'
    and isnull(s2.in_out,'Out') = 'Out'

Maybe you want a full outer join instead of a left, to make it look exactly like your desired output. I'd try it with both and see which you prefer.

As a few have mentioned, without the isnull fuction, you may as well have inner joined.

There are a few other places you can apply isnull() to potentially change the output, too. In the join, for example.