CODEWITHSUNDEEP
mysqlsqlmergeduplicates
marly
marly

Reputation: 55

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

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.

Upvotes: 0

Views: 1147

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 2

Related Questions