CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Santino
Santino

Reputation: 815

create columns from values in sql query

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?

Upvotes: 0

Views: 37

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 2

Related Questions