1

I am working with PostgreSQL 9.3, and I have this:

PARENT_TABLE

ID | NAME
1  | N_A
2  | N_B
3  | N_C

CHILD_TABLE

ID | PARENT_TABLE_ID | KEY | VALUE
1  | 1               | K_A | V_A
2  | 1               | K_B | V_B
3  | 1               | K_C | V_C
5  | 2               | K_A | V_D
6  | 2               | K_C | V_E
7  | 3               | K_A | V_F
8  | 3               | K_B | V_G
9  | 3               | K_C | V_H

Note that I might add K_D in KEY's, it's completely dynamic.

What I want is a query that returns me the following:

QUERY_TABLE

ID | NAME | K_A | K_B | K_C | others K_...
1  | N_A  | V_A | V_B | V_C | ...
2  | N_B  | V_D |     | V_E | ...
3  | N_C  | V_F | V_G | V_H | ...

Is this possible to do ? If so, how ?

Community
  • 1
  • 1
Gustavo Semião-Lobo
  • 2,468
  • 3
  • 18
  • 26
  • `crosstab()` can be used for this. http://www.postgresql.org/docs/9.3/static/tablefunc.html – pozs Apr 09 '14 at 11:35
  • But I still need to specify the name of the columns in my query, right? If I have a child table with KEY_D, do I need to add that column to crosstab ? – Gustavo Semião-Lobo Apr 09 '14 at 11:40
  • 2
    Yes. You cannot ask for *whatever key in my child table, use that as a column*. The planner needs to know about the column count and each column's type. – pozs Apr 09 '14 at 11:54

1 Answers1

0

Since there can be values missing, you need the "safe" form of crosstab() with the column names as second parameter:

SELECT * FROM crosstab(
       'SELECT p.id, p.name, c.key, c."value"
        FROM   parent_table p
        LEFT   JOIN child_table c ON c.parent_table_id = p.id
        ORDER  BY 1'

      ,$$VALUES ('K_A'::text), ('K_B'), ('K_C')$$)
AS t (id int, name text, k_a text, k_b text, k_c text;  -- use actual data types

Details in this related answer:
PostgreSQL Crosstab Query

About adding "extra" columns:
Pivot on Multiple Columns using Tablefunc

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228