repeated posts

I have a database where I collect student information from three different tables.
When I write a select case to see all the student information, the problem is that a student can have more than onte contact person from ex AF. How can I see all this information as one record?

I wrote like this:

Select distinct Studieinfo.PersNR, Elev.Fornamn + ' ' + Elev.Efternamn AS Namn, Studieinfo.Startvecka, Studieinfo.slutvecka,
Studieinfo.startdatum, Studieinfo.slutdatum, Studieinfo.Kursort, Studieinfo.Studietid, Studieinfo.Forlangning,
Studieinfo.beraknad_studietid, Studieinfo.mal, Studieinfo.delrapport, Studieinfo.moduler,
KontaktPersoner_FK.Fornamn + ' ' + KontaktPersoner_FK.Efternamn AS KontaktFK, KontaktPersoner_AF.Fornamn + ' ' +KontaktPersoner_AF.Efternamn AS KontaktAF
From Studieinfo, KontaktPersoner_FK, Kontakt_FK, KontaktPersoner_AF, Kontakt_AF, Elev
WHERE Elev.PersNR=Kontakt_FK.PersNR
and Elev.PersNR=Kontakt_AF.PersNR
and Elev.PersNR=Studieinfo.PersNR
and Elev.PersNR='691215-3638'
and Kontakt_FK.KontaktNR_FK=KontaktPersoner_FK.Kontakt NR_FK
and Kontakt_AF.KontaktNR_AF=KontaktPersoner_AF.Kontakt NR_AF
goIf you have defined contact types (Mothe, Father, Guardian, ParoleOfficer...) then you can write a CROSSTAB query to do this. Look it up in Books Online for instructions. Otherwise you may need to use a cursor to loop through related records and concatenate multiple contact records into a single character string. A user-defined function would be ideal for this.

I'd also say that this type of formatting (which is purely for the sake of appearance) is often best delegated to the reporting interface (crystal, VB, Excel, Access, ect...). In a sense, when you try to formulate a query like this you are asking a relational database to be non-relational.

blindman|||Thanks for your advice. I was actually thinking of correcting it within the asp on the page. Like you suggested but was just wondering if it was possible to do with the sql.