Re: Tricky SQL question

From: Noah Wollowick <nwollowi_at_eecs.tufts.edu>
Date: 1998/11/04
Message-ID: <Pine.GSO.3.95.981104150404.28367A-100000@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 CST