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?

Is it only one Punch In and one Punch Out per day?

Felix Pamittan
– Felix Pamittan

2015-09-17 23:32:30 +00:00
Commented Sep 17, 2015 at 23:32 — Felix Pamittan
– Felix Pamittan, Commented Sep 17, 2015 at 23:32
yes, only one activity per day

Santino
– Santino

2015-09-17 23:33:58 +00:00
Commented Sep 17, 2015 at 23:33 — Santino
– Santino, Commented Sep 17, 2015 at 23:33
possible duplicate of Explanation of self-joins

theB
– theB

2015-09-18 00:45:19 +00:00
Commented Sep 18, 2015 at 0:45 — theB
– theB, Commented Sep 18, 2015 at 0:45

Felix Pamittan · Accepted Answer · 2015-09-17 23:37:56Z

2

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

answered Sep 17, 2015 at 23:37

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

create columns from values in sql query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related