CODEWITHSUNDEEP
sqlpostgresqlrowcrosstab
user1252871
user1252871

Reputation: 21

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        [email protected]  ....
2    1        VARCHAR(255)    name         Jhon              ....

list_table
id
1

I need a result like this:

id email             name  ....
1  [email protected]  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?

Upvotes: 2

Views: 14681

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659197

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. See:

But the column definition list must be in sync with the second parameter anyway. So it only makes limited sense to make one dynamic and the other one static. It's only justified to catch varying column_name - that still sort to fit the same output column definition list.

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)
  LANGUAGE c STABLE STRICT AS
  '$libdir/tablefunc','crosstab_hash';

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

Upvotes: 10

Related Questions