Content-Class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Table transform query
Date: Tue, 18 Sep 2007 15:12:25 +0200
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CF1F@poweredge.attiksystem.ch>
Thread-Topic: Table transform query
thread-index: Acf59ZVMHKWd76BHSS2ChG5uHC6axg==
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>

Hi,

I'm trying to find out how to transform this kind of table data (history
of rental data in a firm):


date  serial  delivery
----------------------
1     1       L
1     2       L
1     3       L
2     1       R
2     2       R
2     4       L
3     5       L
3     3       R
3     4       R
4     1       L


... into this:


serial  dateL  dateR
--------------------
1       1      2
1       4      =20
2       1      2
3       1      3
4       2      3
5       3      =20


Basically, data on table 1 means:=20

- on date 1, product with serial 1 is sent to the customer
- on date 1, product with serial 2 is sent to the customer
- on date 1, product with serial 3 is sent to the customer
- on date 2, product with serial 1 comes back
...

On table 2, data means:

- Product with serial 1 is sent to the customer on date 1, and comes
back on date 2
- Product with serial 1 is sent to the customer on date 4, and hasn't
come back yet
...


Do you think there is a generic SQL solution to this problem, like
crosstab or pivot table?

I'm thinking of doing things in a "procedural" plpgsql manner...

Any idea is welcome.

Thanks!

Philippe