WITH
    Data (MFR_TYPE_ID, MFR_NAME)
    AS
        (SELECT '100-1234', 'Apple' FROM DUAL
         UNION ALL
         SELECT '100-1234', 'Nokia' FROM DUAL
         UNION ALL
         SELECT '100-1244', 'Samsung' FROM DUAL
         UNION ALL
         SELECT '100-1245', 'Facebook' FROM DUAL
         UNION ALL
         SELECT '100-1245', 'Microsoft' FROM DUAL
         UNION ALL
         SELECT '100-1245', 'DELL' FROM DUAL)
SELECT *
  FROM Data;


--- Expected Result 
WITH
    RESULT (MFR_TYPE, MFR_NAME)
    AS
        (SELECT 'Multiple', 'Apple,Nokia' FROM DUAL
         UNION ALL
         SELECT 'Single', 'Samsung' FROM DUAL
         UNION ALL
         SELECT 'Multiple', 'Facebook,Microsoft,DELL' FROM DUAL)
SELECT MFR_TYPE, MFR_NAME
  FROM Result;

SQL> col MFR_NAMES format a50
SQL> WITH
  2      Data (MFR_TYPE_ID, MFR_NAME)
  3      AS
  4          (SELECT '100-1234', 'Apple' FROM DUAL
  5           UNION ALL
  6           SELECT '100-1234', 'Nokia' FROM DUAL
  7           UNION ALL
  8           SELECT '100-1244', 'Samsung' FROM DUAL
  9           UNION ALL
 10           SELECT '100-1245', 'Facebook' FROM DUAL
 11           UNION ALL
 12           SELECT '100-1245', 'Microsoft' FROM DUAL
 13           UNION ALL
 14           SELECT '100-1245', 'DELL' FROM DUAL)
 15  SELECT case
 16           when count(*) = 1 then 'Single'
 17           when count(*) > 1 then 'Multîple'
 18         end MFR_TYPE,
 19         listagg(MFR_NAME,',') within group (order by MFR_NAME) MFR_NAMES
 20  FROM Data
 21  group by MFR_TYPE_ID
 22  /
MFR_TYPE MFR_NAMES
-------- --------------------------------------------------
Multîple Apple,Nokia
Single   Samsung
Multîple DELL,Facebook,Microsoft

3 rows selected.

WITH
    Data (MFR_TYPE_ID, MFR_NAME)
    AS
        (SELECT '100-1234', 'Apple' FROM DUAL
         UNION ALL
         SELECT '100-1234', 'Nokia' FROM DUAL
         UNION ALL
         SELECT '100-1244', 'Samsung' FROM DUAL
         UNION ALL
         SELECT '100-1245', 'Facebook' FROM DUAL
         UNION ALL
         SELECT '100-1245', 'Microsoft' FROM DUAL
         UNION ALL
         SELECT '100-1245', 'DELL' FROM DUAL
        ),
   T AS (
         SELECT  ROW_NUMBER() OVER(PARTITION BY MFR_TYPE_ID ORDER BY 1) RN,
                 CASE COUNT(*) OVER(PARTITION BY MFR_TYPE_ID)
                   WHEN 1 THEN 'Single'
                   ELSE 'Multiple'
                 END MFR_TYPE,
                 LISTAGG(MFR_NAME,',') WITHIN GROUP(ORDER BY MFR_NAME) OVER(PARTITION BY MFR_TYPE_ID) MFR_NAME
           FROM  DATA
        )
SELECT  MFR_TYPE,
        MFR_NAME
  FROM  T
  WHERE RN = 1
/

MFR_TYPE MFR_NAME
-------- ------------------------------
Multiple Apple,Nokia
Single   Samsung
Multiple DELL,Facebook,Microsoft

SQL>