How to move ID of duplicate names to a separate column
I have a MySQL table with two duplicate names, how can I separate the IDs of the duplicate names into a separate 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
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