列值作为psql查询中的列名 [英] column values as column names in thepsql query
问题描述
基于此我有一个表,该表的值将成为PostgreSQL查询结果的列名。
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 ...
每次添加新字段时,col1上的字段数都会更改。
为此,我需要生成一个函数,该函数将以这种方式以动态方式返回结果。
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.
这样做是:
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中添加新名称时,我都无法更新查询。
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?
推荐答案
对于PostgreSQL 9.4 +
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;
这篇关于列值作为psql查询中的列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!