Re: Turning column into row

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant