Postgres Pro
Downloads
Home   >   mailing lists

Cross tabulations - Mailing list pgsql-sql

From Muhyiddin A.M Hayat
Subject Cross tabulations
Date
Msg-id 012501c4b65f$9b70b620$4f00a8c0@middinks
Whole thread Raw
Responses Re: Cross tabulations
List pgsql-sql
Tree view
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  
........
........
........

 
 
 
 

pgsql-sql by date:

Previous
From: Thomas F.O'Connell
Date:
Subject: Re: help with to_date and to_char
Next
From: Greg Stark
Date:
Subject: Re: Cross tabulations