Q:

Group By Result Into a List

I am trying to list all of the rental properties a manager manages from this schema:
Create Table Rental_Property(property_number int Primary Key, managerId  
int, Foreign Key(managerId) References manage(managerId));

Here my procedure:
Create Or Replace Procedure supervisor_properties
As
        list_of_properties varchar (300) := ' ';
Begin
        Select 'Manager' || ': ' || managerId || ' ' || property_number
        Into list_of_properties
        From Rental_Property
        Group By managerId;
End;

The part I am having trouble is the procedure above group by will group all the tuples that have the same managerId together. Now how do I print out the results something like this:
Manager m1: Rental_Prop1, Rental_Prop2, Rental_Prop3
Manager m2: Rental_Prop9, Rental_Prop6, Rental_Prop4

A:

You can use list_agg():
    Select (managerId || ' ' ||
            list_agg(property_number, ' ') within group (order by property_number)
           )
    Into list_of_properties
    From Rental_Property
    Group By managerId;

The only issue is that the into is putting the value into a variable . . . and this will generate an error if the group by has multiple managers.
Start with this query:
Select managerId,
       list_agg(property_number, ' ') within group (order by property_number) as properties
From Rental_Property
Group By managerId;

EDIT:
I see.  If you want to print the values:
Create Or Replace Procedure supervisor_properties
As
Begin
    for x in (Select managerId,
                     list_agg(property_number, ' ') within group (order by property_number) as properties
              From Rental_Property
              Group By managerId
             )
    loop
        dbms_output.put_line(x.managerId || ' ' || x.properties);
    end loop;
End;

