Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 9CF03475F3F
	for <pgsql-sql@postgresql.org>; Wed, 22 May 2002 11:27:08 -0400 (EDT)
Received: from temp.joelburton.com (unknown [209.190.238.101])
	by postgresql.org (Postfix) with ESMTP id 7AEF94759B9
	for <pgsql-sql@postgresql.org>; Wed, 22 May 2002 11:22:26 -0400 (EDT)
Received: from joeltf0d13cyyp (unknown [209.190.238.100])
	by temp.joelburton.com (Postfix) with SMTP
	id 806DA2BE13; Wed, 22 May 2002 10:54:35 -0400 (EDT)
From: "Joel Burton" <joel@joelburton.com>
To: "Wm. G. Urquhart" <wgu@wurquhart.co.uk>, "Tille, Andreas" <TilleA@rki.de>
Cc: "PostgreSQL SQL" <pgsql-sql@postgresql.org>
Subject: Re: Turning column into row
Date: Wed, 22 May 2002 09:11:29 -0400
Message-ID: <JGEPJNMCKODMDHGOBKDNMEAECPAA.joel@joelburton.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
In-Reply-To: <Pine.LNX.4.44.0205221335160.5109-100000@mailer.wurquhart.co.uk>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Archive-Number: 200205/245
X-Sequence-Number: 7880

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Wm. G. Urquhart
> Sent: Wednesday, May 22, 2002 8:41 AM
> To: Tille, Andreas
> Cc: PostgreSQL SQL
> Subject: Re: [SQL] Turning column into row
>
>
> On Wed, 22 May 2002, Tille, Andreas wrote:
>
> > I have a Table of certain items
> >
> >    create table item (
> >        IdItem      int,
> >        Item        varchar(64),
> >        ...
> >    );
> >
> > a lookuptable which defines some properties
> >
> >    create table property (
> >        IdProperty  int,
> >        Property    varchar(64)
> >    );
> >
> > and a table which defines the different properties which are
> possible for
> > a certain item:
> >
> >    create table tlkp_item_property (
> >        IdItem     int,
> >        IdProperty int
> >    );
> >
> > So I can easily select all the properties of a certain item in a table
> > where the columns contain the properties.  But I want to have an output
> > of the kind
> >
> >     Item 1: Property 1, Property 2, Property 3, ...
> >     Item 2: <Properties of Item 2>
> >        ...
> >
> > So I have to turn the different properties according to one item into
> > one field insead of one column.

If you can have the properties listed in one field, rather than in separate
fields, you can use a list aggregator, such as the one at:
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=13
9

SELECT Item.iditem, comma(property) FROM Item JOIN Tlkpitem_Property USING
(iditem) JOIN Property USING (idproperty);

If this won't work, let us know. There are SELECT statements that will
create the output in separate fields, they're just either slow or require
minor modifications to your tables.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant