Question 89209 in PostgreSQL Question posted in
The official documentation can be found here.

convert three records into a single record in postgresql and add values by creating new columns

Here is my base table and i am trying to have a single record for each "app_id".

Ex: "XYZ" has 3 records and would like to convert the same to single record and copy the values to the newly added columns (2ndrank_app_status, 2ndrank_balance, 3rdrank_app_status, 3rdrank_balance)

app_id    app_status  balance      rank

 xyz          1          500         1
 xyz          1          200         2
 xyz          1          200         3
 abc          2          300         1
 abc          9          200         2
 klm          1          800         1

Expected output

app_id    app_status  balance    rank  2ndrank_app_status 2ndrank_balance 3rdrank_app_status   3rdrank_balance

 xyz          1         500        1              1              200           1                200  

 abc          2         300        1              9              200           null               null     

 klm          1         800        1          null              null           null               null
Tags:

2

Answers


  1. 0

    You can simple self join your table a couple times, and get desired outcome.

    select t1.app_id, t1.app_status, t1.balance,
t2.app_status sec_stat, t2.balance sec_bal,
t3.app_status thr_stat, t3.balance thr_bal
from table t1
left join table t2 on (t2.app_id=t1.app_id and t2.rank = 2)
left join table t3 on (t3.app_id=t1.app_id and t3.rank = 3)
where t1.rank = 1
    Login or Signup to reply.
  2. 0

    You can do it using GROUP BY and MAX CASE WHEN :

    select app_id, 
  MAX(case when rank = 1 then app_status end) as app_status,
  MAX(case when rank = 1 then balance end) as balance,
  MIN(rank) as rank,
  MAX(case when rank = 2 then app_status end) as secondRank_app_status,
  MAX(case when rank = 2 then balance end) as secondRank_balance,
  MAX(case when rank = 3 then app_status end) as thirdRank_app_status,
  MAX(case when rank = 3 then balance end) as thirdRank_balance
from mytable
group by app_id

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search