From: Gabriel Dovalo Carril <dovalo@terra.es>
To: Tille, Andreas <TilleA@rki.de>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Turning column into row
Date: Wed, 22 May 2002 13:17:53 +0200
Message-ID: <3CEB7E61.7C999A87@terra.es> (raw)
References: <Pine.LNX.4.44.0205220806200.8932-100000@wr-linux02.rki.ivbb.bund.de>
Hi,
> 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.
You need to build dinamically the SQL query.
First you run:
Select * from property Order by idproperty;
And with data obtained you can build a query like this:
Select item.item,
max(case when property.idproperty = 0 then property.property else
''::text end) as p0,
max(case when property.idproperty = 1 then property.property else
''::text end) as p1
From item, property, tlkp_item_property
Where item.iditem = tlkp_item_property.iditem and
tlkp_item_property.idproperty = property.idproperty
Group by item.item;
*-------------------------------
I have tried it with this:
prueba=# select * from item;
iditem | item
--------+-------
2 | item2
1 | item1
0 | item0
(3 rows)
prueba=# select * from property;
idproperty | property
------------+----------
0 | pro0
1 | pro1
(2 rows)
prueba=# select * from tlkp_item_property;
iditem | idproperty
--------+------------
0 | 0
1 | 1
1 | 0
(3
rows)
prueba# select item.item,
max(case when property.idproperty = 0 then property.property else
''::text end) as p0,
max(case when property.idproperty = 1 then property.property else
''::text end) as p1
from item, property, tlkp_item_property
where item.iditem = tlkp_item_property.iditem and
tlkp_item_property.idproperty = property.idproperty
Group by item.item;
item | p0 | p1
-------+------+------
item0 | pro0 |
item1 | pro0 | pro1
(2 rows)
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: dovalo@terra.es, TilleA@rki.de
Subject: Re: Turning column into row
In-Reply-To: <3CEB7E61.7C999A87@terra.es>
* 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