Add fields to view from select (transpose fields)

Question

I've got a table with following structure:

CREATE TABLE test
(
  id integer,
  field character varying, 
  value character varying
);

field     value
------    -----
field1    value1
field2    value2
field3    value3
...       ...

I need to transpose the field value as the fields in a view and the value of the field with be the "value" field's value. Example of what I need:

field1    field2    field3      ...
------    -----     ------      ----
value1    value2    value3      ...

I have no idea on how to start building the create view statement.

Answer 1

For the simple case you present, build an array from your sorted values and unnest one by one in the SELECT list

SELECT arr[1] AS field1
     , arr[2] AS field2
     , arr[3] AS field3
FROM  (SELECT ARRAY(SELECT value FROM test ORDER BY field) AS arr) sub;

Obviously, this is not dynamic, but only works for given fields. You did not actually ask for "dynamic" - except that you added the tag dynamic-sql.

For more complex operations use crosstab() from the additional module tablefunc. Detailed instructions:

• PostgreSQL Crosstab Query

Still not "dynamic". A completely dynamic function is hardly possible since SQL demands to know the return type at the time of the function call. If the return type is dynamic, you need two steps:

1. build your query dynamically
2. execute it.

But there may be something for you here:

• Dynamic alternative to pivot with CASE and GROUP BY

Answer 2

it is not possible to have dynamic column lists. at the time you issue a SELECT the target list has to be clear. so you cannot just add a column somewhere just because some value is added.

Answer 3

I think you are looking for something like pivot. PostgreSQL is able to do something like that with a tablefunc. There you could use crosstab() But having said: This is not complete dynamic and therefore not fitting your question 100%.