Oracle SQL Transpose Multiple Record
Posted on 2006-06-13
Hoping this is relatively simple, but my SQL is not up to it.
We have a peice of SQL that returns up to 2 of the latest address records for students.
e.g. SELECT * FROM (SELECT stud_id, surname, forename, dob, address1, address2, address3,
ROW_NUMBER() OVER (PARTITION BY stud_id ORDER BY address.start_date DESC) row_num
FROM student, addresses
WHERE student.stud_id = addresses.entity_id(+))
WHERE row_num <3
If a student has 2 or more addresses then 2 records are returned for that student.
I need to return a single record per student with both addresses shown (as seperate fields) within that record
e.g. stud_id, surname, forename, dob, address1, address2, address3, address1-2, address2-2, address3-2
Any ideas would be much appreciated (Oracle ver is 9.02 if that helps). Would prefer a pure SQL solution if possible.