Re: Tricky SQL question
Date: 1998/11/04
Message-ID: <Pine.GSO.3.95.981104150404.28367A-100000_at_allegro>#1/1
Joost:
Why don't you try writing the SQL using an 'Oracle inline function', and
then derive the column inside of this function, as i've psuedocoded up
below.
Hope this helps, Regards,
Noah Wollowick
noahclerk_at_aol.com
IT Consultant
Example:
select person,
person_id, Fnct_Get_Attribs_For_Person(person_id)from person_table
where <Where condition>
Then, code the following (pseudocoded) stored procedure in PL/SQL:
create or replace function Fnct_Get_Attribs_For_Person(l_person IN
person_table.person_id%TYPE) RETURN VARCHAR2 IS
DECLARE
/* Declare cursor to select the attributes for a given id passed into
the function */
declare cursor c1 as
select attribute from attribute_lookup_table where id = l_person;
cTemp varchar2(/* Define length here */); cReturnString varchar2(/* Define maxlength here */);
bFirstFetch Boolean;
BEGIN
bFirstFetch := True;
cReturnString := '' /* Initialize cReturn String */
open cursor c1; /* Open the cursor. */
/* You can get the while loop syntax, etc. in an Oracle reference guide */
while (c1 is not at the end of the cursor) */
fetch cursor c1 into cTemp; /* Fetch c1 into the temp variable */ if bFirstFetch = True then cReturnString := cTemp; else cReturnString := cReturnString || ', ' || cTemp end if;
end loop; /* End while loop */
return cReturnString; /* Return the formatted string to the calling SQL statement */
END; On Wed, 4 Nov 1998, 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 Wed Nov 04 1998 - 00:00:00 CET