Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id DE8484760D8
	for <pgsql-sql@postgresql.org>; Wed, 22 May 2002 08:18:58 -0400 (EDT)
Received: from tsmtp1.mail.isp (mailhost2.teleline.es [195.235.113.141])
	by postgresql.org (Postfix) with ESMTP id CDCAC475E64
	for <pgsql-sql@postgresql.org>; Wed, 22 May 2002 08:18:47 -0400 (EDT)
Received: from terra.es ([213.98.70.162]) by tsmtp1.mail.isp
	(Netscape Messaging Server 4.15 tsmtp1 Mar 14 2002 21:29:48)
	with ESMTP id GWII5300.NYK; Wed, 22 May 2002 14:17:27 +0200
Message-ID: <3CEB7E61.7C999A87@terra.es>
Date: Wed, 22 May 2002 13:17:53 +0200
From: Gabriel Dovalo Carril <dovalo@terra.es>
X-Mailer: Mozilla 4.76 [es] (X11; U; Linux 2.4.0-4GB i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "Tille, Andreas" <TilleA@rki.de>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Turning column into row
References: 
 <Pine.LNX.4.44.0205220806200.8932-100000@wr-linux02.rki.ivbb.bund.de>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200205/227
X-Sequence-Number: 7862

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)