PostgreSQL row to columns

问题:

I'm trying to create a dynamic system that allows the users to import lists of data from Excel so I need to have dynamic columns, ex:

custom_columns_table
id   list_id  data_type       column_name  data              ....
1    1        VARCHAR(255)    email        [e-mail]  ....
2    1        VARCHAR(255)    name         Jhon              ....

list_table
id
1

I need a result like this:

id email             name  ....
1  [e-mail]  Jhon  ....

I have found some examples using crosstab but I don`t know if it will work in this case.

Does anyone know how can I do this?

回答1:

First off, the crosstab() family of functions is not installed in standard PostgreSQL. You need to install the extension tablefunc for this. In PostgreSQL 9.1 you would simply:

CREATE EXTENSION tablefunc;

For older versions have a look at this related answer.

Query

The query could look like this:

SELECT *
FROM   crosstab (
        'SELECT l.id
               ,c.column_name
               ,c.data
         FROM   custom_columns_table c
         JOIN   list_table l ON l.id = c.list_id
         ORDER  BY 1',

        'SELECT DISTINCT column_name
         FROM   custom_columns_table
         ORDER  BY 1')
AS tbl (
    id integer
   ,email text
   ,name text
   );

I use the form of crosstab() with two parameters, because that allows for missing attributes. Like, when a person has no email. Then this form will return NULL for the email column. Detailed explanation:

• PostgreSQL Crosstab Query

Function

Or create a function so you don't have to supply a column definition list for every call:

CREATE OR REPLACE FUNCTION f_mycross(text, text)
  RETURNS TABLE (
    id integer
   ,email text
   ,name text)
  AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT;

Call:

SELECT * FROM f_mycross(
       'SELECT l.id
              ,c.column_name
              ,c.data
        FROM   custom_columns_table c
        JOIN   list_table l ON l.id = c.list_id
        ORDER  BY 1',

       'SELECT DISTINCT column_name
        FROM   custom_columns_table
        ORDER  BY 1')