SELECT
    spriden_pidm pidm,
    spriden_id unum,
    camp.goremal_email_address camp,
    listagg(fnln.goremal_email_address,',') WITHIN GROUP (ORDER BY spriden_pidm) fnln,
    listagg(pers.goremal_email_address,',') WITHIN GROUP (ORDER BY spriden_pidm) pers,
    listagg(par.goremal_email_address,',') WITHIN GROUP (ORDER BY spriden_pidm) par,
    listagg(apc.goremal_email_address,',') WITHIN GROUP (ORDER BY spriden_pidm) apc,
    listagg(apc.goremal_email_address,',') WITHIN GROUP (ORDER BY spriden_pidm) recr,
    listagg(alum.goremal_email_address,',') WITHIN GROUP (ORDER BY spriden_pidm) alum,
    listagg(calb.goremal_email_address,',') WITHIN GROUP (ORDER BY spriden_pidm) calb,
    listagg(other.goremal_email_address,',') WITHIN GROUP (ORDER BY spriden_pidm) other
FROM spriden
LEFT JOIN goremal camp ON camp.goremal_pidm = spriden_pidm AND camp.goremal_emal_code = 'CAMP'
LEFT JOIN goremal fnln ON fnln.goremal_pidm = spriden_pidm AND fnln.goremal_emal_code = 'FNLN' AND fnln.goremal_status_ind = 'A'
LEFT JOIN goremal pers ON pers.goremal_pidm = spriden_pidm AND pers.goremal_emal_code = 'PERS' AND pers.goremal_status_ind = 'A'
LEFT JOIN goremal par ON par.goremal_pidm = spriden_pidm AND par.goremal_emal_code IN ('PAR','PAR1','PAR2') AND par.goremal_status_ind = 'A'
LEFT JOIN goremal apc ON apc.goremal_pidm = spriden_pidm AND apc.goremal_emal_code = 'APC' AND apc.goremal_status_ind = 'A'
LEFT JOIN goremal recr ON recr.goremal_pidm = spriden_pidm AND recr.goremal_emal_code = 'RECR' AND recr.goremal_status_ind = 'A'
LEFT JOIN goremal alum ON alum.goremal_pidm = spriden_pidm AND alum.goremal_emal_code = 'ALUM' AND alum.goremal_Status_ind = 'A'
LEFT JOIN goremal calb ON calb.goremal_pidm = spriden_pidm AND calb.goremal_emal_code = 'CALB' AND calb.goremal_status_ind = 'A'
LEFT JOIN goremal other ON other.goremal_pidm = spriden_pidm AND other.goremal_emal_code IN ('BUSI','PCOL','OTHR','EMRG','WEB','SOAR','EM') AND other.goremal_status_ind = 'A'
WHERE spriden_change_ind IS NULL
GROUP BY
spriden_pidm, spriden_id,camp.goremal_email_address