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?

Converting rows to columns is usually refered to a piviting. Look up pivot tables in the archives. I have posted examples on this in the past as has Billy V.

HTH -- Mark D Powell -- Received on Mon Nov 29 2004 - 08:46:46 CST