Search code examples
sqlselectoracle-sqldeveloper

SQL Select to Merge Key Field When Row is a Duplicate

I have my database as below:

ID | First | Surname | DOB
-----------------------------
01 | Homer | Simpson | 12-May
02 | Homer | Simpson | 12-May
03 | Marge | Bouvier | 19-Mar

I am looking to run a SQL select query on the database, to create results where a new unique ID is created by merging the unique fields of the duplicated rows?

ID      | First | Surname | DOB
----------------------------------
01 / 02 | Homer | Simpson | 12-May
03      | Marge | Bouvier | 19-Mar
Solution

  • You are asking for some sort of string aggregation function. This depends on the database, but the idea is:

    select listagg(id, ' / ') within group (order by id) as ids,
       first, surname, dob
from t
group by first, surname, dob;

    The name of the listagg() function -- and exact syntax -- vary by database.