Re: Turning column into row

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