I need help in Postgres transforming rows data into columns. Like I have id and value column, id column will have value as 'Account_Number' and 'Account_Holder_Name' and value column corresponding to the actual value. The below table is the representation of the data I will hold in a table and belongs to the custom fields, so the id column may also contain more field names and the value field will contain the actual value of that field

Table: trans

Table: trans_custom_fields

Now I am want to transform this table data in the below format which can be used in the join query too and shown as a single record.

Table: join resultset

Please help here, I have spent a lot of time researching and hit and run but have not succeeded. Now the only hope is this platform.

CodePudding user response：

--https://www.postgresql.org/docs/current/sql-keywords-appendix.html
--data init. value kind of ambiguous for me.  I change to _value.
--obviously now, the id and _value will be as text type.
begin;
create table trans_custom_fields(id text, _value text,transid integer );
insert into trans_custom_fields values('ACCOUNT_HOLDER_NAME','Manoj Sharma',1);
insert into trans_custom_fields values('ACCOUNT_NUMBER',    '113565TTE44656',   1);
insert into trans_custom_fields values(    'RECIPT_NUMBER', '24324.',   1);
insert into trans_custom_fields values(   'ACCOUNT_HOLDER_NAME',    'Another User', 2);
insert into trans_custom_fields values('ACCOUNT_NUMBER',    '35546656TRFG23',   2);
insert into trans_custom_fields values('RECIPT_NUMBER', '24324686', 2);
commit;

--step 1 create a new temp table a, transform trans_custom_fields to a jsonb table.
create temp table a as (select to_jsonb(row) 
        from ( select id, _value, transid from trans_custom_fields t ) row);

--step 2 rename '_value' to ACCOUNT_HOLDER_NAME,ACCOUNT_NUMBER, RECIPT_NUMBER RESPECTIVELY
update a set to_jsonb 
= to_jsonb - '_value' || jsonb_build_object( to_jsonb->>'id',to_jsonb -> '_value') returning *;

--step 3 remove the 'id' key value pair
update a set to_jsonb =  to_jsonb - 'id';

--step 4. aggregate to one jsonb based o transid
select jsonb_agg (to_jsonb - 'transid' ),a.to_jsonb->>'transid'
    as transid from a group by a.to_jsonb ->>'transid';

--step5 get the result.
with a as (select jsonb_agg (to_jsonb - 'transid' ),
        a.to_jsonb->>'transid' as transid
        from a group by a.to_jsonb ->>'transid')

select transid,
       jsonb_agg -> 0 ->> 'ACCOUNT_HOLDER_NAME' as ACCOUNT_HOLDER_NAME,
       jsonb_agg -> 1 ->> 'ACCOUNT_NUMBER' as ACCOUNT_NUMBER,
       jsonb_agg -> 2 ->> 'RECIPT_NUMBER' as RECIPT_NUMBER
from a;

step2 reference: PostgreSQL rename attribute in jsonb field

CodePudding user response：

Below is the query to create the respective table and insert some data.

begin;
create table trans_custom_fields(id text, _value text,transid integer );
insert into trans_custom_fields values('ACCOUNT_HOLDER_NAME','Manoj Sharma',1);
insert into trans_custom_fields values('ACCOUNT_NUMBER',    '113565TTE44656',   1);
insert into trans_custom_fields values(    'RECIPT_NUMBER', '24324.',   1);
insert into trans_custom_fields values(   'ACCOUNT_HOLDER_NAME',    'Another User', 2);
insert into trans_custom_fields values('ACCOUNT_NUMBER',    '35546656TRFG23',   2);
insert into trans_custom_fields values('RECIPT_NUMBER', '24324686', 2);
commit;

Now I want to do the transformation for this data and here I am going to use crosstab feature of Postgres.

SELECT *
FROM   crosstab(
   'SELECT transid, id, _value
    FROM   trans_custom_fields
    ORDER  BY 1,2'
   ) AS ct (transid int, ACCOUNT_HOLDER_NAME text, ACCOUNT_NUMBER text);

I am really thankful to crosstab example for just helping me understand and write my own answer for my question, also thank @mark who does provide the queries and resolution but that fit better as of now.