~[wc:commonscripts] "Student Id","Last Name","First Name","Type","Address","City","State","Zip","Home Phone","Work Phone","Cell Phone","Email","LivesWith","IsCustodial" ~[TLIST_SQL; WITH addr AS ( SELECT paa.personid, pa.street, pa.city, cs.code AS state, pa.postalcode, ROW_NUMBER() OVER ( PARTITION BY paa.personid ORDER BY paa.addresspriorityorder ) AS row_id FROM personaddressassoc paa JOIN personaddress pa ON pa.personaddressid = paa.personaddressid LEFT JOIN codeset cs ON cs.codesetid = pa.statescodesetid ), phone AS ( SELECT ppna.personid, cs.code as phonetype, pn.phonenumber, ROW_NUMBER() OVER ( PARTITION BY ppna.personid, cs.code ORDER BY ppna.phonenumberpriorityorder ) AS row_id FROM personphonenumberassoc ppna JOIN phonenumber pn ON pn.phonenumberid = ppna.phonenumberid LEFT JOIN codeset cs ON cs.codesetid = ppna.phonetypecodesetid ), email AS ( SELECT peaa.personid, ea.emailaddress, ROW_NUMBER() OVER ( PARTITION BY peaa.personid ORDER BY peaa.emailaddresspriorityorder ) AS row_id FROM personemailaddressassoc peaa JOIN emailaddress ea ON ea.emailaddressid = peaa.emailaddressid ) SELECT REPLACE(s.student_number, '"', '""'), REPLACE(p.lastname, '"', '""'), REPLACE(p.firstname, '"', '""'), ROW_NUMBER() OVER ( PARTITION BY sca.studentdcid ORDER BY sca.studentdcid, sca.contactpriorityorder ) AS priority, REPLACE(a.street, '"', '""'), REPLACE(a.city, '"', '""'), REPLACE(a.state, '"', '""'), REPLACE(a.postalcode, '"', '""'), REPLACE(h.phonenumber, '"', '""'), REPLACE(w.phonenumber, '"', '""'), REPLACE(m.phonenumber, '"', '""'), REPLACE(e.emailaddress, '"', '""'), REPLACE(scd.liveswithflg, '"', '""'), REPLACE(scd.iscustodial, '"', '""') FROM students s JOIN studentcontactassoc sca ON sca.studentdcid = s.dcid JOIN studentcontactdetail scd ON scd.studentcontactassocid = sca.studentcontactassocid AND scd.isactive = 1 JOIN person p ON p.id = sca.personid AND p.isactive = 1 LEFT JOIN codeset cs ON cs.codesetid = sca.currreltypecodesetid LEFT JOIN phone h ON h.personid = p.id AND h.phonetype = 'Home' AND h.row_id = 1 LEFT JOIN phone w ON w.personid = p.id AND w.phonetype = 'Work' AND w.row_id = 1 LEFT JOIN phone m ON m.personid = p.id AND m.phonetype = 'Mobile' AND m.row_id = 1 LEFT JOIN addr a ON a.personid = p.id AND a.row_id = 1 LEFT JOIN email e ON e.personid = p.id AND e.row_id = 1 WHERE s.student_number IS NOT NULL ORDER BY s.student_number, sca.contactpriorityorder ~[if.~(gpv.offset)=] OFFSET 0 ROWS FETCH NEXT 50000 ROWS ONLY [else] OFFSET ~(gpv.offset) ROWS FETCH NEXT 50000 ROWS ONLY [/if] ;]"~(1;T)","~(2;T)","~(3;T)","~(4;T)","~(5;T)","~(6;T)","~(7;T)","~(8;T)","~(9;T)","~(10;T)","~(11;T)","~(12;T)","~(13;T)","~(14;T)" [/TLIST_SQL]