sql - 使用动态列创建表

Question

我正在使用 PostgreSQL 9.1，我有以下表格:

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)

设想以下表格:

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' |
 ---------------------------------------

现在我想得到以下表格:

 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         |
 ----------------------------------------------------------

我正在尝试了解什么是进入低谷的最佳程序。我已经阅读了 tablefunc 文档，但我无法弄清楚如何动态地制作该过程，因为类别表上将有 N 个条目。一些方向将不胜感激。

Answer 1

解决方法:

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
 );

测试设置:

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');

结果:

  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>

您的问题的解决方案是使用带有两个参数 的crosstab() 变体。

查询输出所有项目。将 WHERE 子句添加到第一个查询以过滤类别。

第二个参数(另一个查询字符串)生成输出列的列表，以便正确分配数据查询(第一个参数)中的 NULL 值。

查看 tablefunc extension 的手册，特别是 crosstab(text, text):

我已经发布了几个关于 tablefunc 模块的 crosstab() 函数的答案。 This search会给你更多的例子和解释。