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)
