You are here

Concat Mom and Dad email columns in PowerSchool using LISTAGG in oracle

We used this to feed to our notification systems. (Absent notifications, Drills, Snow days, etc...)

The query:

SELECT s.dcid,
listagg(ea.EMAILADDRESS, ',')
WITHIN GROUP (ORDER BY s.dcid, ea.emailaddress) "Guardian_email"

FROM
STUDENTS s, STUDENTCONTACTASSOC sca, person p, PERSONEMAILADDRESSASSOC pea, EMAILADDRESS ea

WHERE
 s.enroll_status=0
     and   sca.studentdcid=s.dcid
     and   sca.personid=p.id
     and   sca.CURRRELTYPECODESETID in (30,26) 
            --and s.student_number=15793
     and pea.EMAILADDRESSID = ea.EMAILADDRESSID
     and p.id=pea.PERSONID
     and pea.EMAILTYPECODESETID in 17
     and s.enroll_status=0
 GROUP BY s.dcid;

The outcome:

DCID Guardian_email
1234 mom@student.com, dadsemail@student.com
1235 mom@student.com, dadsemail@student.com
1236 mom@student.com
(This one respresents a student where there isn't a "dad"email)
1237 mom@student.com, dadsemail@student.com

Yay!!!