given is the following database scheme and I am wondering how to build the appropriate query on a Postgres system.
It is defined that Items can have multiple (1 -> n) Attributes which relation is specified within the Relation table. This relation table also stores a value related to this connection. Also items and attributes can be added or removed any time, but the relation table will always only contain valid item and attribute ids.
Item ---- id (primary key), name Attribute --------- id (primary key), name Item_Attribute_Relation ----------------------- Item.id, Attribute.id, value
Now my task is to return a (sparse) table where items are shown as rows and where each attribute is given as a column. As an example following table shows the Attributes (A, B, C, and D) and the Items (a, b, c, and d). The entries relate to the value in the Item_Attribute_Relation. It is possible that an attribute (column) is NULL for every item and that an item has no attributes at all.
| A | B | C | D | -------------------- a | x | x | y | | b | x | z | | | c | w | | | | d | | | | |
Unfortunately my SQL knowledge is rusty and Postgres does not support PIVOT to my knowledge. Also I am not able (probably due to missing knowledge) to dynamically generate the columns when using the Postgres crosstab() function. My gut tells me that this is a common problem. Therefore help by providing the correct wording or by posting links to this problem explanations is also welcome.