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)