Derived Tables

Hi. We have to create an export from our system to be imported into another system. To get the data out we need to create some SQl but we're struggling a bit.

I presently have the following code

SELECT ProSolution.dbo.StudentDetail.RefNo, ProSolution.dbo.StudentDetail.FirstForename, ProSolution.dbo.StudentDetail.Surname,

ProSolution.dbo.StudentDetail.MobileTel, ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID, ProSolution.dbo.Enrolment.CompletionStatusID,

ProSolution.dbo.Offering.Code

FROM ProSolution.dbo.StudentDetail INNER JOIN

ProSolution.dbo.Enrolment ON ProSolution.dbo.StudentDetail.StudentDetailID = ProSolution.dbo.Enrolment.StudentDetailID INNER JOIN

ProSolution.dbo.Offering ON ProSolution.dbo.Enrolment.OfferingID = ProSolution.dbo.Offering.OfferingID

WHERE (ProSolution.dbo.StudentDetail.AcademicYearID = '06/07') AND (ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID = '9') AND

(ProSolution.dbo.Enrolment.CompletionStatusID = '1')




The above code returns the data one line per course but we need it to be one line per student with all their courses on one line too, like follows.

567897 Tom Smith 07111 111111 TCFT1 CKSAN1 DHICS

Can anyone give us any guidance please?

Thanks
Chip