pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Muhyiddin A.M Hayat <middink@indo.net.id>
To: pgsql-sql@postgresql.org
Subject: Cross tabulations
Date: Wed, 20 Oct 2004 12:43:00 +0800
Message-ID: <012501c4b65f$9b70b620$4f00a8c0@middinks> (raw)

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   
........
........
........

view thread (3+ messages)  latest in thread

Message-ID: <012501c4b65f$9b70b620$4f00a8c0@middinks>
Permalink:  ../012501c4b65f$9b70b620$4f00a8c0@middinks/
Also on:    postgresql.org/message-id/012501c4b65f$9b70b620$4f00a8c0@middinks

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: middink@indo.net.id
  Subject: Re: Cross tabulations
  In-Reply-To: <012501c4b65f$9b70b620$4f00a8c0@middinks>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox