Re: Turning column into row

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)