Значения столбцов в качестве имен столбцов в запросе thepsql

Question

Исходя из этого, у меня есть таблица, значения которой будут именами столбцов результата запроса PostgreSQL.

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

Теперь я хочу, чтобы результат запроса выглядел так

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

Количество полей в столбце col1 изменяется каждый раз, когда добавляется новое поле. Для этого мне нужно сгенерировать функцию, которая будет динамически возвращать результаты таким образом.

Это сделал это:

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

но мне нужно, чтобы он был динамическим, потому что имена col1 могут быть большим списком, поэтому я не могу обновлять запрос каждый раз, когда новое имя добавляется в col1.

Я проверил, как это сделать, используя сводную таблицу, я попытался следовать этому примеру, но там также хорошо известны поля, пожалуйста, кто-нибудь может мне помочь?

Answer 1

Для 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 $$;

Использование (с некоторыми результатами отладки):

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;

Answer 2

Этот запрос полностью отвечает вашим потребностям

SELECT
  table_id.id,
  table_name.name,
  table_name2.name2,
  table_name3.name3
FROM left join (SELECT DISTINCT id FROM mytable ) table_id 
left join (SELECT id,  MAX(col2 ) AS name FROM mytable where col1 = 'name' GROUP BY id) table_name on table_name2.id = table_id.id
left join (SELECT id,  MAX(col2 ) AS name2 FROM mytable where col1 = 'name2' GROUP BY id) table_name2 on table_name2.id = table_id.id
left join (SELECT id,  MAX(col2 ) AS name3 FROM mytable where col1 = 'name3' GROUP BY id) table_name3  on table_name2.id = table_id.id