From: Joel Burton <joel@joelburton.com>
To: Wm. G. Urquhart <wgu@wurquhart.co.uk>
To: 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> (raw)
In-Reply-To: <Pine.LNX.4.44.0205221335160.5109-100000@mailer.wurquhart.co.uk>
> -----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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joel@joelburton.com, wgu@wurquhart.co.uk, TilleA@rki.de
Subject: Re: Turning column into row
In-Reply-To: <JGEPJNMCKODMDHGOBKDNMEAECPAA.joel@joelburton.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox