From: | "Joel Burton" <joel(at)joelburton(dot)com> |
---|---|
To: | "Wm(dot) G(dot) Urquhart" <wgu(at)wurquhart(dot)co(dot)uk>, "Tille, Andreas" <TilleA(at)rki(dot)de> |
Cc: | "PostgreSQL SQL" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Turning column into row |
Date: | 2002-05-22 13:11:29 |
Message-ID: | JGEPJNMCKODMDHGOBKDNMEAECPAA.joel@joelburton.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> -----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
From | Date | Subject | |
---|---|---|---|
Next Message | Joel Burton | 2002-05-22 13:14:25 | Re: Turning column into row |
Previous Message | Tille, Andreas | 2002-05-22 12:48:20 | Re: Turning column into row |