2

Based on this I have a table which has values which will be the column names of the PostgreSQL query result.

id    col1     col2
----------------------
0      name    ax
0      name2   bx
0      name3   cx
1      name    dx
1      name2   ex
1      name2   fx
...     ...     ...

Now I want the result of the query to look like this 

id   name    name2     name3   ...
0    ax      bx         cx     ...
1    dx      ex         fx     ...

The number of fields on col1 is changed each time that a new field is added. So for that I need to generate a function that will return the results in that way in a dynamic way.

This did that:

SELECT
  id,
  /* if col1 matches the name string of this CASE, return col2, otherwise return NULL */
  /* Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id */
  MAX(CASE WHEN (col1 = 'name') THEN col2 ELSE NULL END) AS name,
  MAX(CASE WHEN (col1 = 'name2') THEN col2 ELSE NULL END) AS name2,
  MAX(CASE WHEN (col1 = 'name3') THEN col2 ELSE NULL END) AS name3
FROM mytable
GROUP BY id

but I need to have it dynamic because the col1 names can be a big list so I cant update the query each time that new name is added in the col1.

I checked how to do that using the pivot table, I tried to follow this example but also there the fields are well known, please can someone help me?

Community
  • 1
  • 1
  • To call the query with dynamic amount of columns you should to use [dynamic command](https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) and [refcursor](https://www.postgresql.org/docs/current/static/plpgsql-cursors.html#AEN66382) or [sql cursor](https://www.postgresql.org/docs/current/static/sql-declare.html) to fetch the result. – Abelisto Nov 07 '16 at 20:31
  • 1
    You listed ODBC as one of your tags... are you bringing this into Excel or Access? If so, you can dynamically link the data via ODBC and let those tools render it the way you ask -- in a pivot table or cross-tab query – Hambone Nov 07 '16 at 23:09

1 Answers1

1

For PostgreSQL 9.4+

-- Test data
create table t(id int, col1 text, col2 text);
insert into t values
  (0, 'name', 'ax'),
  (0, 'name2', 'bx'),
  (0, 'name3', 'cx'),
  (1, 'name', 'dx'),
  (1, 'name2', 'ex'),
  (1, 'name3', 'fx');

create or replace function fn_pivot(
  p_sql text,
  p_row_field text,
  p_col_field text,
  p_data_field text,
  p_cursor refcursor) returns refcursor language plpgsql as $$
declare
  cols text[];
  a text[];
  q text;
  --f text;
begin
  -- Get dynamic columns
  q := format('select array_agg(distinct %s::text) from (%s) t', p_col_field, p_sql);
  execute q into cols;
  -- Generate SELECT part
  select array_agg(format('%s filter (where %s::text = %L) as %I', p_data_field, p_col_field, x, x)) into a from unnest(cols) as t(x);
  q := format('%s, %s', p_row_field, array_to_string(a, ', '));
  -- Complete the whole statement
  q := format('select %s from (%s) t group by %s order by %s', q, p_sql, p_row_field, p_row_field);
  raise info '%', q;
  open p_cursor for execute q;
  return p_cursor;
end $$;

Usage (with some debug output):

nd@postgres=# start transaction;
START TRANSACTION
*nd@postgres=# select * from fn_pivot('select * from t', 'id', 'col1', 'max(col2)', 'cur');
INFO:  select id, max(col2) filter (where col1::text = 'name') as name, max(col2) filter (where col1::text = 'name2') as name2, max(col2) filter (where col1::text = 'name3') as name3 from (select * from t) t group by id order by id
╔══════════╗
║ fn_pivot ║
╠══════════╣
║ cur      ║
╚══════════╝
(1 row)

*nd@postgres=# fetch all in cur;
╔════╤══════╤═══════╤═══════╗
║ id │ name │ name2 │ name3 ║
╠════╪══════╪═══════╪═══════╣
║  0 │ ax   │ bx    │ cx    ║
║  1 │ dx   │ ex    │ fx    ║
╚════╧══════╧═══════╧═══════╝
(2 rows)

*nd@postgres=# rollback;
Abelisto
  • 14,826
  • 2
  • 33
  • 41