Merge duplicate rows and populate new columns with duplicate data in SQL

Question

Newbie to SQL here and need a bit of guidance. Any would be appreciated!

So say I have a SQL table as follows:

id  email            preference
1   [email protected]   cake
1   [email protected]   fruit
1   [email protected]   cheese
1   [email protected]   sauce
2   [email protected]   cake
2   [email protected]   sauce
3   [email protected]   tea

What I want to do is merge the duplicate records (based on id and email), but also use the duplicate data in the "preferences" column to populate some additional columns in a new table. Basically I want to get to this:

id  email           preference1   preference2   preference3   preference4 
1   [email protected]  cake          fruit         cheese        sauce
2   [email protected]  cake          sauce         null          null
2   [email protected]  tea           null          null          null

Currently at a bit of a loss of how to get here.

Have you considered another table with a different structure like (id, person_id, preference)? This can allow you to see all the preferences of an individual and respect 2nd Normal Form. — NanoBit
– NanoBit, Commented Dec 9, 2020 at 3:18

Gordon Linoff · Accepted Answer · 2020-12-09 03:13:11Z

2

If you know you have four preferences, you can use conditional aggregation with row_number():

select id, email,
       max(case when seqnum = 1 then preference end) as preference_1,
       max(case when seqnum = 2 then preference end) as preference_2,
       max(case when seqnum = 3 then preference end) as preference_3,
       max(case when seqnum = 4 then preference end) as preference_4
from (select t.*,
             row_number() over (partition by id, email order by preference) as seqnum
      from t
     ) t
group by id, email;

answered Dec 9, 2020 at 3:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge duplicate rows and populate new columns with duplicate data in SQL

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