Oracle 21c

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

WITH data AS (
   
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'aa'   attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'zz' attr3    FROM dual
   
), d1 AS (

SELECT product_id, 'Issue with attr1' descr 
   FROM data
   GROUP BY product_id 
   HAVING COUNT(DISTINCT attr1) > 1

), d2 AS (
      
   SELECT DISTINCT d1.product_id, d1.descr, data.attr1, COUNT(attr1) OVER (PARTITION BY attr1) cnt
   FROM d1
   INNER JOIN data
      ON d1.product_id = data.product_id
   
)

SELECT product_id, descr, LISTAGG(attr1 || '(' || cnt || ')', ', ') WITHIN GROUP (ORDER BY product_id) value_count
FROM d2
GROUP BY product_id, descr
;

PRODUCT_ID	DESCR	VALUE_COUNT
101	Issue with attr1	a(2), aa(1)

WITH data AS (
   
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'zz'  attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'aa'   attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'zz' attr3    FROM dual
   
), count_ as (SELECT product_id, COUNT(DISTINCT attr1) attr1
  , COUNT(DISTINCT attr2) attr2
  , COUNT(DISTINCT attr3) attr3
   FROM data
   GROUP BY product_id ),
d2 AS (
      
   SELECT DISTINCT count_.product_id, 'Issue with attr1' descr , data.attr1, COUNT(data.attr1) OVER (PARTITION BY data.attr1) cnt
   FROM count_
   INNER JOIN data
      ON count_.product_id = data.product_id AND count_.attr1 > 1 
   UNION ALL
     SELECT DISTINCT count_.product_id, 'Issue with attr2' descr , data.attr2, COUNT(data.attr2) OVER (PARTITION BY data.attr2) cnt
   FROM count_
   INNER JOIN data
      ON count_.product_id = data.product_id AND count_.attr2 > 1 
  UNION ALL
     SELECT DISTINCT count_.product_id, 'Issue with attr3' descr , data.attr3, COUNT(data.attr3) OVER (PARTITION BY data.attr3) cnt
   FROM count_
   INNER JOIN data
      ON count_.product_id = data.product_id AND count_.attr3 > 1 
)
SELECT product_id, descr, LISTAGG(attr1 || '(' || cnt || ')', ', ') WITHIN GROUP (ORDER BY product_id) value_count
FROM d2
GROUP BY product_id, descr
;

PRODUCT_ID	DESCR	VALUE_COUNT
101	Issue with attr1	a(2), aa(1)
101	Issue with attr3	z(3), zz(2)
103	Issue with attr3	z(3), zz(2)