Transform Postgres rows data into columns based on condition

Question

I am wanting to use Postgres transforming rows data into columns. 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

id	type	booking_date
1	Deposit	2022-02-02
2	Withdraw	2022-02-03

Table: trans_custom_fields

id	value	transId
ACCOUNT_HOLDER_NAME	Manoj Sharma	1
ACCOUNT_NUMBER	113565TTE44656	1
RECIPT_NUMBER	24324.	1
ACCOUNT_HOLDER_NAME	Another User	2
ACCOUNT_NUMBER	35546656TRFG23	2
RECIPT_NUMBER	24324686	2

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

ACCOUNT_HOLDER_NAME	ACCOUNT_NUMBER	RECIPT_NUMBER	transId
Manoj Sharma	113565TTE44656	24324	1
Another User	35546656TRFG23	24324686	2

What can I try next?

Hi there Manoj. If you can, try to using phrases in your questions that reinforce your confidence in your problem solving abilities as an engineer. Indeed, doing this will make you a better engineer - pessimistic thinking clouds one's ability to reason clearly. I have removed the material about Stack Overflow being your last chance to solve this problem - that is plainly untrue, and is just coercive helplessness. — halfer, Apr 07 '22 at 21:40
Now that is a mystifying question, Manoj. The reason for one of the downvotes has been placed above, and still you ask why it was downvoted? I am baffled by your query. Nevertheless, perhaps [this discussion](https://meta.stackoverflow.com/questions/366264/how-can-we-encourage-new-authors-to-ask-confident-questions) on the Meta site may also be useful. — halfer, Apr 14 '22 at 18:26

score 1 · Answer 1 · answered Mar 12 '22 at 10:06

--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

score -1 · Accepted Answer · answered Mar 12 '22 at 11:24

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.

Why this answer is downvoted. This solved my problem and shared so that someone who is looking for the same can utilise it. This downvote somehow shows that this is not the correct answer. Please add your comment on who has done a downvote. This help other to understand why this is downvoted and should correct the answer if this is wrong. — Manoj Sharma, Apr 14 '22 at 08:02

Transform Postgres rows data into columns based on condition

2 Answers2