create columns from values in sql query

Question

I have a data in this format

employee_id date-time           activity
-------------------------------------------
23          01-06-2015 08:00    Punch In
23          01-06-2015 15:23    Punch Out
27          01-08-2015 08:12    Punch In
27          01-08-2015 14:13    Punch Out

I want this.

employee_id punch_in_time       punch_out_time
-------------------------------------------------
23          01-06-2015 08:00    01-06-2015 15:23
27          01-08-2015 08:12    01-08-2015 14:13

I want to write a view from the table to create the data format shown above. Can anyone help? Should I use a pivot?

possible duplicate of [Explanation of self-joins](http://stackoverflow.com/questions/2458519/explanation-of-self-joins) — theB, Sep 18 '15 at 00:45

score 2 · Accepted Answer · answered Sep 17 '15 at 23:37

You can do this using conditional aggregation:

SELECT
    employee_id,
    punch_in_time   = MIN(CASE WHEN activity = 'Punch In' THEN [date-time] END),
    punch_out_time  = MAX(CASE WHEN activity = 'Punch Out' THEN [date-time] END)
FROM tbl
GROUP BY employee_id, CAST([date-time] AS DATE)
ORDER BY employee_id, punch_in_time

create columns from values in sql query

1 Answers1