參照方法，拆分再 group by 

/**方法1**/  
with Tab  
as  
(select 1 as Col1,N'a,b,c' as Col2  from dual union all  
select 2,N'd,e' from dual union all  
select 3,N'f'   from dual )  
SELECT   
      Col1,substr(Col2,lev,instr(Col2||',',',',lev)-lev) as Col2  
from Tab   
    ,(SELECT LEVEL lev FROM DUAL CONNECT BY LEVEL<=100)  
WHERE   
    substr(','||Col2,lev,1)=',' /** 条件可换为 instr(','||Col2,',',lev)=lev**/  
order by Col1  
  
/**方法2  
REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)    
__srcstr        ：检索字符串  
__pattern      ：匹配模式  
__position     ：搜索srcstr的起始位置（默认为1）  
__occurrence：搜索第几次出现匹配模式的字符串（默认为1）  
__modifier     ：检索模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）    
**/  
  
with Tab  
as  
(select 1 as Col1,N'a,b,c' as Col2  from dual union all  
select 2,N'd,e' from dual union all  
select 3,N'f'   from dual )  
SELECT   
    Col1,REGEXP_SUBSTR(Col2,'[^,]+',1,lev)  
FROM Tab,  
    (SELECT LEVEL lev FROM dual CONNECT BY LEVEL <= 100) b  
WHERE (LENGTH(Col2)-LENGTH(REPLACE(Col2,',','')))+1 >=lev  
ORDER BY Col1,lev

------解决方案--------------------
SQL code
WITH t1 AS
     (SELECT '张三' NAME, '梨' str
        FROM DUAL
      UNION ALL
      SELECT '李四', '苹果'
        FROM DUAL
      UNION ALL
      SELECT '王五', '梨;苹果'
        FROM DUAL)
SELECT   str, COUNT (str) num
    FROM (SELECT REGEXP_SUBSTR (a.str, '[^;]+', 1, b.l) str
            FROM t1 a,
                 (SELECT     LEVEL l
                        FROM DUAL
                  CONNECT BY LEVEL <= 10) b
           WHERE REGEXP_SUBSTR (a.str, '[^;]+', 1, b.l) IS NOT NULL)
GROUP BY str

------解决方案--------------------
SQL code

SQL> WITH t AS (
  2      SELECT '1' tid,'apple,pear,banana' fruit FROM DUAL UNION ALL
  3      SELECT '2' tid,'apple,banana' fruit FROM DUAL UNION ALL
  4      SELECT '3' tid,'apple,pear' fruit FROM DUAL UNION ALL
  5      SELECT '4' tid,'banana' fruit FROM DUAL
  6  )
  7  SELECT m.fruit,
  8         COUNT(*) num
  9    FROM (SELECT tid,
 10                 RTRIM(REGEXP_SUBSTR(fruit || ',', '.*?' || ',', 1, LEVEL), ',') AS fruit
 11            FROM t
 12          CONNECT BY tid = CONNECT_BY_ROOT(tid)
 13                 AND LEVEL <=
 14                     LENGTH(REGEXP_REPLACE(fruit || ',', '[^' || ',' || ']', ''))) m
 15   GROUP BY m.fruit
 16   ORDER BY m.fruit
 17  ;

FRUIT                                                                           NUM
------------------------------------ ----------
apple                                                                             3
banana                                                                            3
pear                                                                              2