Search code examples
sqlsql-serverdivide

How to combine results from one column and then split into 2 columns

I've encountered some difficulty about combining and then splitting a column

Table: persona

Name                    date        time                InOut
--------------------------------------------------------------
MANCA GIOVANNI          2019-12-06  10:50:00.0000000    I
MANCA GIOVANNI          2019-12-06  12:55:00.0000000    O
HAMPIT RICKY            2019-12-06  12:55:00.0000000    O
HAMPIT RICKY            2019-12-06  10:50:00.0000000    I
RODRIGUEZ CARLOS DANIEL 2019-12-06  10:50:00.0000000    I
RODRIGUEZ CARLOS DANIEL 2019-12-06  12:55:00.0000000    O

Based on that table I would like output like below

Name                    date        InTime              OutTime
--------------------------------------------------------------
MANCA GIOVANNI          2019-12-06  10:50:00.0000000    12:55:00.0000000
HAMPIT RICKY            2019-12-06  10:50:00.0000000    12:55:00.0000000
RODRIGUEZ CARLOS DANIEL 2019-12-06  10:50:00.0000000    12:55:00.0000000

This is the code I tried

SELECT Name, date, time as InTime, time as OutTime
FROM persona 
WHERE InOut = 'I' and InOut = 'O'

Thank you

Solution

  • Do a GROUP BY. Use case expressions to separate In from Out.

    SELECT Name, date,
       max(case when InOut = 'I' then time end) as Intime,
       max(case when InOut = 'O' then time end) as Outime
FROM persona 
group by Name, date

    (Will perhaps need some tweaking if a person has several in's or out's the same date.)