DF768 DF768 - 6 months ago 6
SQL Question

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

Answer

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;
Comments