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:
Pretty neat huh?! \m/
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, Teacher | Goku, Dragon | 04/29/2007 | F | ||||
| Awesome, Teacher | Stark, Tony | 11/17/2008 | F |
|
Pretty neat huh?! \m/