How to move duplicate IDs to seperate column

January 13, 2022

I have a mySQL table with two duplicate names, how can I seperate the IDs of the duplicate names into a seperate column?

   ---------------------------------
   | id   | name       | surname   |
   ---------------------------------     
   |  557 | Logan      | Smith     |
   | 1052 | Logan      | Smith     |
   ---------------------------------

For example, like this:

   ----------------------------------------
   | id   |  id2  | name      | surname   |
   ----------------------------------------     
   | 557  |  1052 | Logan     | Smith     |
   ----------------------------------------

This is my current SQL statement to retrieve the current results in the first table:

SELECT userid as id, name, surname
FROM user
WHERE name = "Logan"
GROUP BY userid

There is only one duplicate at most for each name.

Thanks

>Solution :

If you are sure that the maximum is always 2… then you could:

SELECT min(userid) as id1, max(userid) as id2, name, surname
FROM user
WHERE name = "Logan"
GROUP BY name, surname

If you want to sofisticate a little bit more the query

SELECT min(userid) as id1,
  case when min(userId) = max(userid) then null else max(userId) end as id2, name, surname
FROM user
WHERE name = "Logan"
GROUP BY name, surname