0

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
Dustin Cook
  • 1,215
  • 5
  • 26
  • 44

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Well that worked very nicely, and very elegant solution. I've never heard of `listagg` before. Every day is a school day! – Dustin Cook Aug 10 '21 at 10:46