英文:
Postgres: pull column value into row header and value
问题
使用Postgres 12.8。我的表以以下方式存储单个流程实例的变量：
我需要为每个ID创建单行，以便列名将是变量名（例如：appealDto、offshoreOnly、dueDate等），值将是变量值。因此，它应该如下所示：
id_ | offshoreOnly | dueDate
------------------------------------------------------------
ecb-sad-23132 | 0 |16356454656
请注意，此表以一种有趣的方式存储变量值。它根据变量的类型使用3个列（double_、long_、text_）中的一个来存储变量值。
我尝试过，但我能做到的最好的是：
select v.id_ proc_id ,
(case when v.name_='dueDate' then v.long_ else null end) due_date,
(case when v.name_='offShoreOnly' then v.long_ else null end) offShoreOnly
from camunda_data.act_ru_variable v
但这没有给我预期的结果。
我还尝试过：
select v.proc_inst_id_::text ,
v.name_::text,
v.long_::text
from camunda_data.act_ru_variable v
where v.proc_def_id_ NOT ILIKE 'StartProcess%'
and v.name_ in ('dueDate','offShoreOnly')
但这给了我：
希望有建议？提前感谢。
英文:
Using Postgres 12.8. My table stores variables for a single process instance in this way:
I need to create single row per id so that the column name will be the variable name (for example: appealDto, offshoreOnly, dueDate etc) and the value will be the variable value. So, it should look like:
id_ | offshoreOnly | dueDate
------------------------------------------------------------
ecb-sad-23132 | 0 |16356454656
Notice that this table has an interesting way of storing variable value. It uses 1 of the 3 columns (double_, long_, text_) to store the variable-value depending on the variable's type.
I tried but best I could do:
select v.id_ proc_id ,
(case when v.name_='dueDate' then v.long_ else null end) due_date,
(case when v.name_='offShoreOnly' then v.long_ else null end) offShoreOnly
from camunda_data.act_ru_variable v
And that did not give me the expected result.
Also tried:
select v.proc_inst_id_::text ,
v.name_::text,
v.long_::text
from camunda_data.act_ru_variable v
where v.proc_def_id_ NOT ILIKE 'StartProcess%'
and v.name_ in ('dueDate','offShoreOnly')
But that gave me :
Any suggestion? Thanks in advance
答案1
得分: 0
你可能需要的内容：
CREATE EXTENSION tablefunc SCHEMA camunda_data;
而最终有效的查询是：
select * from crosstab (
$$select v.proc_inst_id_::text ,
v.name_::text,
v.long_::text
from camunda_data.act_ru_variable v
where v.proc_def_id_ NOT ILIKE 'StartAppealProcess%'
and v.name_ in ('dueDate','offShoreOnly')$$)
as (proc_id text, due_date text, offshore text);
英文:
You may need :
CREATE EXTENSION tablefunc SCHEMA camunda_data;
And the query that finally worked:
select * from crosstab (
$$select v.proc_inst_id_::text ,
v.name_::text,
v.long_::text
from camunda_data.act_ru_variable v
where v.proc_def_id_ NOT ILIKE 'StartAppealProcess%'
and v.name_ in ('dueDate','offShoreOnly')$$)
as (proc_id text, due_date text, offshore text);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论