Cross tabulations

From: "Muhyiddin A(dot)M Hayat" <middink(at)indo(dot)net(dot)id>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Cross tabulations
Date: 2004-10-20 04:43:00
Message-ID: 012501c4b65f$9b70b620$4f00a8c0@middinks
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Dear all,

I need to do something similar to a cross tabulation, but without any
aggregation.

I have below table

id | employee_id | state | check_time
----+-------------+-----------+--------------------
21 | 1 | In | 2004-10-12 21:37:13
22 | 1 | Break Out | 2004-10-12 21:37:31
23 | 1 | Break In | 2004-10-12 21:37:42
24 | 1 | Out | 2004-10-12 21:37:50
25 | 1 | In | 2004-10-13 19:20:36
26 | 1 | In | 2004-10-14 01:33:48
27 | 1 | Break Out | 2004-10-14 01:59:15
28 | 1 | Break In | 2004-10-14 03:15:45
29 | 1 | Out | 2004-10-14 03:17:23
30 | 3 | In | 2004-10-14 03:17:43
31 | 3 | Break Out | 2004-10-14 19:32:34
32 | 2 | In | 2004-10-14 20:34:15
33 | 3 | In | 2004-10-15 02:01:28
34 | 3 | Break Out | 2004-10-15 02:02:07
35 | 3 | In | 2004-10-16 02:06:43
36 | 1 | In | 2004-10-16 02:07:33
37 | 1 | Break Out | 2004-10-16 02:09:09
38 | 1 | Break In | 2004-10-16 04:10:21
39 | 1 | Out | 2004-10-16 04:12:27
40 | 3 | Break Out | 2004-10-16 21:38:22

I need something like this:

date | employee_id | in | break_out | break_id | out
-----------+-------------+----------+-----------+----------+----------
2004-10-12 | 1 | 21:37:13 | 21:37:31 | 21:37:42 |21:37:50
2004-10-14 | 1 | 01:33:48 | 01:59:15 | 03:15:45 |03:17:23
2004-10-14 | 3 | 03:17:43 | 19:32:34 | 03:15:45 |03:17:23
........
........
........

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Greg Stark 2004-10-20 06:35:25 Re: Cross tabulations
Previous Message Thomas F.O'Connell 2004-10-20 03:29:36 Re: help with to_date and to_char