Re: Tricky SQL question

If you had a column of attribute sequence value on the attributes table so you could see the first attribute of person 123 was Man and the second for person 123 was Actor. You could use MAX(Decode(attribute_sequence,1,attribute,null))||max(decode,attribute_sequence,2,attribute,null))

ad nauseum or to the max. This way you could update the table or make a view without using any PL/SQL.

joe B>

Joost Ouwerkerk wrote:

> Given two tables: people (Person, ID) and attributes (ID, attribute)
>
> A person can have any number of attributes:
>
> ID Attribute
> --- ---------
> 123 Man
> 123 Actor
> 123 Consumer
>
> How can I get the following output using only SQL (SQL*Plus):
>
> Person ID Attributes
> -------------------------- --- ---------------------------------
> Joe Jones 123 Man, Actor, Consumer
> Mary Walsh 234 Woman, Comedian, Writer, Director
> Jackie Chan 456 Man, Martial Artist
> Larry Sanders 678 Man, Comedian, Producer
>
> The attributes must be on one line and separated by commas. Is this
> even possible?
>
> Joost Ouwerkerk
> Hospital For Sick Children Foundation
> Toronto, Canada
Received on Mon Nov 09 1998 - 00:00:00 CET