Selecting table of properties as columns

Question

I have two tables, things and properties:

CREATE TABLE things (
  id SERIAL PRIMARY KEY
);

CREATE TABLE properties (
  thing_id INT,
  key TEXT,
  value TEXT
);

I want to select from things and join rows from properties as columns. For example, say I have the following:

INSERT INTO things DEFAULT_VALUES;  -- suppose id is 1
INSERT INTO properties (thing_id, key, value) VALUES
  (1, 'height', '5'),
  (1, 'width', '6'),
  (1, 'length', '7');

How can I select from things with height, width, and length as columns?

Also, I don't want to specifically select height, width, and length, but any rows that may be inside properties.

Answer 1

For just three columns:

SELECT t.thing_id
      ,max(CASE WHEN p.key = 'height' THEN p.value END) AS height
      ,max(CASE WHEN p.key = 'width'  THEN p.value END) AS width
      ,max(CASE WHEN p.key = 'length' THEN p.value END) AS length
FROM   things t
LEFT   JOIN properties p ON p.thing_id = t.id
WHERE  t.id = 1
GROUP  BY 1;

Or use crosstab() from the additional module tablefunc which is typically faster, and shorter for long lists of attributes:

SELECT * FROM crosstab(
      'SELECT t.thing_id, p.key, p.value
       FROM   things t
       LEFT   JOIN properties p ON p.thing_id = t.id
       WHERE  t.id = 1
       ORDER  BY 1'

      ,$$VALUES ('height'::text), ('width'), ('length')$$) -- add more
AS ct (thing_id int, height int, width int, length int);   -- add more

Types have to match. Detailed explanation:

• PostgreSQL Crosstab Query

A completely dynamic list of columns cannot be achieved in a single query. I have tried many times. Here is what can be done:

• Dynamic alternative to pivot with CASE and GROUP BY