I need the result from the select statement as multiple birth data for a single identifier

Question

APPLICANT_ID    DATE_OF_BIRTH
206209579       04/29/82
206209579       04/29/82
203276426       06/01/69
203276426       02/03/96
203276426       06/02/99

I need a result like

206209579       04/29/82,04/29/82
203276426       06/01/69,02/03/96

please offer it full

Answer 1

In Oracle 11g and above, you can use listagg:

SELECT   applicant_id,
         LISTAGG(date_of_birth) WITHIN GROUP (ORDER BY date_of_birth)
FROM     my_table
GROUP BY applicant_id 

Answer 2

Alternatively, for versions of Oracle prior to 11g , where it is LISTAGGnot supported, you can use the ROW_NUMBER () and SYS_CONNECT_BY_PATH functions.

Test case:

SQL> SELECT * FROM table1;

APPLICANT_ID DATE_OF_B
------------ ---------
   206209579 29-APR-82
   206209579 29-APR-82
   203276426 01-JUN-69
   203276426 03-FEB-96
   203276426 02-JUN-99

SQL>
SQL> SELECT applicant_id,
  2         LTRIM(MAX(SYS_CONNECT_BY_PATH(date_of_birth,','))
  3         KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
  4  FROM   (SELECT applicant_id,
  5                 date_of_birth,
  6                 ROW_NUMBER() OVER (PARTITION BY applicant_id ORDER BY date_of_birth) AS curr,
  7                 ROW_NUMBER() OVER (PARTITION BY applicant_id ORDER BY date_of_birth) -1 AS prev
  8          FROM   table1)
  9  GROUP BY applicant_id
 10  CONNECT BY prev = PRIOR curr AND applicant_id = PRIOR applicant_id
 11  START WITH curr = 1;

APPLICANT_ID EMPLOYEES
------------ --------------------------------------------------
   203276426 01-JUN-69,03-FEB-96,02-JUN-99
   206209579 29-APR-82,29-APR-82

SQL>

Tim's article has other ways here .