Create table with dynamic columns

Question

I'm working on PostgreSQL 9.1, and I have the following tables:

element(element_id int, name varchar, category_id int)

category(category_id int, name varchar)

characteristic(characteristic_id int, name varchar, unit varchar)

category_characteristic(characteristic_id, category_id)

element_characteristic(element_id, characteristic_id, value)

Imagine the following tables:

elements
 ---------------------
|id|name    |category |
-----------------------
|1 |'item 1'|1        |
|2 |'item 2'|2        |
|3 |'item 3'|1        |
 ---------------------

category
 ----------------
|id|name         |
|----------------|
|1 | 'category 1'|
|2 | 'category 2'|
 ----------------

characteristic
 --------------------------------
|id|name               |unit     |
 --------------------------------
|1 |'characteristic 1' | 'unit 1'| 
|2 |'characteristic 2' | 'unit 2'|
|3 |'characteristic 3' | 'unit 3'|
|4 |'characteristic 4' | 'unit 4'|
|5 |'characteristic 5' | 'unit 5'|
|6 |'characteristic 6' | 'unit 6'|
 --------------------------------

category_characteristic
 ------------------------------
|category_id|characteristic_id |
|------------------------------
|1          |1                 |
|1          |2                 |   
|1          |4                 |
|1          |5                 |
|2          |1                 |
|2          |3                 |
|2          |5                 |
 ------------------------------

element_characteristic
 ---------------------------------------
|id_element|id_characteristic|value     |
|---------------------------------------|
|1         |1                |'value a' |
|1         |2                |'value b' |
|1         |4                |'value c' |
|1         |5                |'value d' |
|2         |1                |'value e' |
|2         |3                |'value f' |
|2         |5                |'value g' |
|3         |1                |'value h' |
|3         |2                |'value i' |
|3         |4                |'value j' |
|3         |5                |'value k' |
 ---------------------------------------

And now I want to get the following tables:

 category 1
 ---------------------------------------------------------------------------
|name   |characteristic 1|characteristic 2|characteristic 4|characteristic 5|
| --------------------------------------------------------------------------|
|item 1 |value a         |value b         |value c         |value d         |
|item 3 |value h         |value i         |value j         |value k         |
 ---------------------------------------------------------------------------

category 2
 -----------------------------------------------------------
|name   |characteristic 1|characteristic 3|characteristic 5|
| ----------------------------------------------------------
|item 2 |value e         |value f         |value g         |
 ----------------------------------------------------------

I'm trying to understand what is the best procedure to go trough. I've read the tablefunc documentation but I just can't figure out how to make that procedures dynamically because there will be N entries on the category table. Some kind of directions will be very appreciated.

Answer 1

Solution:

SELECT *
FROM   crosstab (
        'SELECT e.name, c.name, ec.value
         FROM   elements e
         JOIN   element_characteristic ec ON ec.id_element = e.id
         JOIN   characteristic c ON c.id = ec.id_characteristic
         ORDER  BY 1, 2',

        'SELECT DISTINCT name
         FROM   characteristic
         ORDER  BY 1')
AS tbl (
  name text
 ,characteristic_1 text
 ,characteristic_2 text
 ,characteristic_3 text
 ,characteristic_4 text
 ,characteristic_5 text
 ,characteristic_6 text
 );

Test setup:

CREATE TEMP TABLE elements(id int, name text, category int);
INSERT INTO elements VALUES
 (1, 'item 1', 1)
,(2, 'item 2', 2)
,(3, 'item 3', 1);

CREATE TEMP TABLE element_characteristic(id_element int
                                        ,id_characteristic int, value text);
INSERT INTO element_characteristic VALUES
 (1,1,'value a')
,(1,2,'value b')
,(1,4,'value c')
,(1,5,'value d')
,(2,1,'value e')
,(2,3,'value f')
,(2,5,'value g')
,(3,1,'value h')
,(3,2,'value i')
,(3,4,'value j')
,(3,5,'value k');

CREATE TEMP TABLE characteristic (id int, name text, unit text);
INSERT INTO characteristic VALUES
 (1,'characteristic 1', 'unit 1')
,(2,'characteristic 2', 'unit 2')
,(3,'characteristic 3', 'unit 3')
,(4,'characteristic 4', 'unit 4')
,(5,'characteristic 5', 'unit 5')
,(6,'characteristic 6', 'unit 6');

Result:

  name | characteristic 1 | characteristic_2 | characteristic_3 | characteristic_4 | characteristic_5 | characteristic_6
-------|------------------|------------------|------------------|------------------|------------------|----------------
item 1 | value a          | value b          | <NULL>           | value c          | value d          | <NULL>
item 2 | value e          | <NULL>           | value f          | <NULL>           | value g          | <NULL>
item 3 | value h          | value i          | <NULL>           | value j          | value k          | <NULL>

The solution for your problem is to use the crosstab() variant with two parameters.

The query outputs all items. Add a WHERE clause to the first query to filter for category.

The second parameter (another query string) produces the list of output columns, so that NULL values in the data query (the first parameter) are assigned correctly.

Check the manual for the tablefunc extension, and in particular crosstab(text, text):

I have posted a couple of answer concerning crosstab() functions of the tablefunc module. This search will give you more examples and explanation.