Convert multiple records into single record in Postgresql

Question

user_id  channel user_type      score  user_band 
user1      0     Primary         577       A         
user1      0     Secondary       574       B    
user1      2     primary         500       c
user2.     0     primary         400       A

I am trying to convert multiple records into single record based on below conditions

For every distinct "user_id" and channel=0 ,select the record with "user_type" = primary record and replace the values of "score" and "user_band" with values of "user_type" = secondary.

Expected output

user_id  channel user_type      score  user_band 
user1      0     Primary         574       B            
user1      2     primary         500       c
user2      0     primary         400       A

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-07-23 19:26:43Z

Schema (PostgreSQL v13)

create table yourtable (user_id varchar(50), channel int, user_type varchar(50), score int, user_band varchar(10));
insert into yourtable values('user1',0,     'Primary',         577,'A');         
insert into yourtable values('user1',0,     'Secondary',       574,'B');    
insert into yourtable values('user1',2,     'primary',         500,'c');
insert into yourtable values('user2',0,     'primary',         400,'A');

Query #1

with cte as
( 
  select *,row_number()over(partition by user_id,channel order by user_type)rn,
  lead(score)over(partition by user_id,channel order by user_type)lead_score,
  lead(user_band)over(partition by user_id,channel order by user_type)lead_user_band
  from yourtable
)
select user_id,channel,user_type, coalesce(lead_score,score) score, coalesce(lead_user_band,user_band) user_band from cte where rn=1;

user_id	channel	user_type	score	user_band
user1	0	Primary	574	B
user1	2	primary	500	c
user2	0	primary	400	A

View on DB Fiddle

Collectives™ on Stack Overflow

Convert multiple records into single record in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related