CodePudding user response:

 with tab1 as (
Select the 'screw' name, amount, 'involvement in the operating room 1 dept from dual union all 
Select the 'screw' name, amount, 20 'involvement in the operating room 2' dept from dual union all 
Select the 'screw' name, amount of 15, 3 ' 'involvement in the operating room dept from dual union all 
Select 'bone plate' name, amount, 100 'involvement in the operating room 1 dept from dual union all 
Select 'bone plate' name, the amount of 200, 2 ' 'involvement in the operating room dept from dual union all 
Select 'bone plate' name, amount, 300 'involvement in the operating room 3' dept from dual union all 
Select 'electrotome' name, amount, 1 'involvement in the operating room 1 dept from dual union all 
Select 'electrotome' name, amount, 2 'involvement in the operating room 2' dept from dual union all 
Select 'electrotome' name, amount 3, 3 ' 'involvement in the operating room dept from dual union all 
Select 'electrotome' name, amount 4, 4 ' 'involvement in the operating room dept from dual 
) 
Select the name, sumcount listagg (dept, ', ') WITHIN GROUP (ORDER BY dept) a 
The from (
SELECT 
The name, 
Dept, 
The sum (amount) OVER (partition by name) sumcount, 
ROW_NUMBER () OVER (partition by the name the order by the amount) an rn 
The FROM 
Tab1) 
Where rn<4 
Group by name, sumcount order by sumcount desc; 

CodePudding user response: