Re: Output formatting - display multiple records on one line

andyza_at_webmail.co.za (Luis) wrote in message news:<69476b6f.0411282256.4702023d_at_posting.google.com>...
> I want to produce a list of people and the qualificatons they have
> obtained. The Oracle 9i database has this structure:
>
> TblPerson { PersonId(PK), PersonName, PersonSurname, PersonTel }
> TblEducation { QualificationId(PK), PersonId(FK), QualificationName,
> DateObtained }
>
> One person can have multiple qualifications. In this instance it
> doesn't matter that a particular qualification can be obtained by more
> than one person and I don't need to cater for that possibility.
>
> I want to generate a spreadsheet of Persons and the qualifications
> they have obtained. I want it to be laid out as follows if the person
> only has one qualification:
>
> PersonId | PersonName | PersonSurname | PersonTel | QualificationId |
> QualificationName | DateObtained
>
> If the person has two qualifications it will look like this:
> PersonId | PersonName | PersonSurname| PersonTel | QualificationId |
> QualificationName | DateObtained | QualificationId | QualificationName
> | DateObtained
>
> And for three qualifications:
> PersonId | PersonName | PersonSurname| PersonTel | QualificationId |
> QualificationName | DateObtained | QualificationId | QualificationName
> | DateObtained | QualificationId | QualificationName | DateObtained
>
> And so on for more qualifications. All the qualifications are listed
> next to each other on one line.
>
> This query:
> SELECT PER.PersonName, PER.PersonId, EDU.PersonId, PER.PersonTel,
> EDU.QualificationId, EDU.QualificationName, EDU.DateObtained
> FROM TblPerson PER, TblEducation EDU
> WHERE ((PER.PersonId = EDU.PersonId(+))
>
> Gives me this result:
>
> Peter Smith,19612121,19612121,234-444,Degree888,BComm,1989/05/24
> Peter Smith,19612121,19612121,234-444,Diploma555,Marketing,1995/06/23
> Mary Jane,19881005,19881005,587-3548,Diploma246,Accounting,1998/10/10
> Jon Brown,19500101,19500101,222-3333,Degree195,Phd,1990/01/01
>
> Peter Smith has two qualifications while the others have one. Two
> lines are created for Peter Smith. I'm stuck at the part where I join
> the two Peter Smith records so that I get his two qualifications on
> one line, ie:
>
> Peter Smith,19612121,19600505,234-444,Degree888,BComm,1989/05/24,Diploma555,Marketing,1995/06/23
> Mary Jane,19881005,19881005,587-3548,Diploma246,Accounting,1998/10/10
> Jon Brown,19500101,19500101,222-3333,Degree195,Phd,1990/01/01
>
> How do I get the two Peter Smith qualifications to be displayed next
> to each other on the same line?

What tool do you use?

With sqlplus, you could execute "break on PER.PersonName" before your query to which you will have to add "order by PER.PersonName". But you can't directly include a second row for the break (for PER.PersonId)...
Then process the output in your program to join the lines. Received on Mon Nov 29 2004 - 08:27:31 CST