------------------------------ Commands Entered ------------------------------
WITH
 sample_data(id, FirstName, LastName, Department) AS (
SELECT 1, 'James',  'Anderson', 'Computer Science'       FROM sysibm.sysdummy1 UNION ALL
SELECT 2, 'James',  'Anderson', 'Information Technology' FROM sysibm.sysdummy1 UNION ALL
SELECT 3, 'James',  'Anderson', 'Information System'     FROM sysibm.sysdummy1 UNION ALL
SELECT 4, 'Robert', 'McCafee',  'Heath Care'             FROM sysibm.sysdummy1 UNION ALL
SELECT 5, 'Robert', 'McCafee',  'Biology'                FROM sysibm.sysdummy1 UNION ALL
SELECT 6, 'Suman',  'Kapoor',   'Compter Science'        FROM sysibm.sysdummy1 UNION ALL
SELECT 7, 'Suman',  'Kapoor',   'Information Technology' FROM sysibm.sysdummy1 UNION ALL
SELECT 8, 'Luke',   'Morety',   'Computer Science'       FROM sysibm.sysdummy1
)
/*
 End of sample data
*/
,numbered_data(
  id, FirstName, LastName, Department
, rn, row_cnt
) AS (
SELECT sample_data.*
     , INTEGER(
       ROWNUMBER()
         OVER(PARTITION BY FirstName, LastName)
       )
     , COUNT(*)
         OVER(PARTITION BY FirstName, LastName)
  FROM sample_data
)
,recursive_CTE(
  id, FirstName, LastName, Department
, rn, row_cnt
) AS (
SELECT id, FirstName, LastName
     , CAST(Department AS VARCHAR(100) )
     , rn, row_cnt
  FROM numbered_data
 WHERE rn = 1
UNION ALL
SELECT pre.id, pre.FirstName, pre.LastName
     , pre.Department || ', ' || new.Department
     , pre.rn + 1
     , pre.row_cnt
  FROM recursive_CTE pre
     , numbered_data new
 WHERE new.FirstName = pre.FirstName
   AND new.LastName  = pre.LastName
   AND new.rn = pre.rn + 1
   AND pre.rn < 1000000000
)
SELECT FirstName, LastName, Department
  FROM recursive_CTE
 WHERE rn = row_cnt
 ORDER BY
       id
;
------------------------------------------------------------------------------

FIRSTNAME LASTNAME DEPARTMENT                                                                                          
--------- -------- ----------------------------------------------------------------------------------------------------
James     Anderson Information System, Information Technology, Computer Science                                        
Robert    McCafee  Biology, Heath Care                                                                                 
Suman     Kapoor   Information Technology, Compter Science                                                             
Luke      Morety   Computer Science                                                                                    

  4 record(s) selected.