Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   Name        VARCHAR(6) NOT NULL PRIMARY KEY
  ,Temperament VARCHAR(28) NOT NULL
);
begin
INSERT INTO mytable(Name,Temperament) VALUES ('Husky','Smart, Loyal, Cute');
INSERT INTO mytable(Name,Temperament) VALUES ('Poodle','Smart, Cute');
INSERT INTO mytable(Name,Temperament) VALUES ('Golden','Cute, Loyal, Caring, Loving');
end;/
1 rows affected

with cte (name, temperament, temp, cnt, lvl) as (
	select 
		name, 
		temperament, 
		regexp_substr (temperament, '[^, ]+', 1, 1) temp, 
		regexp_count(temperament, ',') cnt,
		1 lvl 
	from mytable
	union all
	select 
		name, 
		temperament, 
		regexp_substr (temperament, '[^, ]+', 1, lvl  + 1), 
		cnt,
		lvl + 1
	from cte
	where lvl <= cnt
)
select temp temperament, listagg(name, ', ') within group(order by name) name, count(*) cnt
from cte
group by temp
order by 1


TEMPERAMENT
NAME
CNT


Caring
Golden
1

Cute
Golden, Husky, Poodle
3

Loving
Golden
1

Loyal
Golden, Husky
2

Smart
Husky, Poodle
2

TEMPERAMENT	NAME	CNT
Caring	Golden	1
Cute	Golden, Husky, Poodle	3
Loving	Golden	1
Loyal	Golden, Husky	2
Smart	Husky, Poodle	2