Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (colname) AS
SELECT 'apple'  FROM DUAL CONNECT BY LEVEL <= 8 UNION ALL
SELECT 'pear'   FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'apple'  FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'orange' FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
SELECT 'grape'  FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 'apple'  FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'grape'  FROM DUAL CONNECT BY LEVEL <= 1;
19 rows affected

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',')
  FROM tablename;


RTRIM(XMLAGG(XMLELEMENT(E,COLNAME,',').EXTRACT('//TEXT()')ORDERBYCOLNAME).GETCLOBVAL(),',')


apple,apple,apple,apple,apple,apple,apple,apple,apple,apple,grape,grape,grape,orange,orange,orange,orange,orange,pear


SELECT LISTAGG(colname, ',') WITHIN GROUP (ORDER BY ROWNUM)
         AS items
FROM   (
  SELECT colname,
         ROWNUM AS rn
  FROM   tablename
)
MATCH_RECOGNIZE(
  ORDER BY rn
  MEASURES
    FIRST(colname) AS colname
  PATTERN (same+)
  DEFINE same AS FIRST(colname) = colname
)


ITEMS


apple,pear,apple,orange,grape,apple,grape


SELECT LISTAGG(colname, ',') WITHIN GROUP (ORDER BY ROWNUM)
         AS items
FROM   (
  SELECT colname,
         LAG(colname) OVER (ORDER BY ROWNUM) AS prev_colname,
         ROWNUM AS rn
  FROM   tablename
)
WHERE rn = 1
OR    prev_colname <> colname


ITEMS


apple,pear,apple,orange,grape,apple,grape