Saturday, April 25, 2015

PowerSchool - Query Oracle DB to get a list of students with their siblings

In Oracle database of PowerSchool, to get a list of students with their siblings who also students, you can use the following query:



The important parts of the above query are:

* Group the students of a same family based on the FAMILY_IDENT column of the STUDENTS table:

SELECT
STUDENTS.FAMILY_IDENT AS FAMILY_IDENT,
'<ul>' || listagg('<li>' || STUDENTS.LASTFIRST || ' (' || STUDENTS.GRADE_LEVEL || ')' || ',</li>', '') 
WITHIN GROUP ( ORDER BY STUDENTS.GRADE_LEVEL DESC, Upper(Trim(STUDENTS.LASTFIRST)) ) || '</ul>'
AS SIBLINGS
FROM STUDENTS
WHERE STUDENTS.ENROLL_STATUS = 0
GROUP BY STUDENTS.FAMILY_IDENT

The result will be something like this:

-------------------------------------------------------------------------------------------------------
| FAMILY_IDENT | SIBLINGS                                                                                    |
-------------------------------------------------------------------------------------------------------
| family01                | <ul><li>Joe Student 1 (4),</li><li>Jane Student (2),</li></ul> |
-------------------------------------------------------------------------------------------------------
| family02                | <ul><li>Joe Student 2 (7),</li></ul>                                           |
-------------------------------------------------------------------------------------------------------
....

* Then we will query the STUDENTS table JOIN with the above results set to get the SIBLINGS column.

* We also need to remove the student which is pointing to from the SIBLINGS:

REPLACE(FA.SIBLINGS, '<li>'||S.LASTFIRST||' ('||S.GRADE_LEVEL||')'||',</li>', '')


The final result will be something like:


Super, TeacherGoku, Dragon 04/29/2007F

    Awesome, TeacherStark, Tony11/17/2008F
    • Stark, Sammuel (8),
    • Stark, Math (6),

    Pretty neat huh?! \m/