Oracle 21c

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

CREATE TABLE table_name (ID, Text) AS
SELECT 1, 'A1 Text - words' FROM DUAL UNION ALL
SELECT 2, 'A1 Text - wordsa' FROM DUAL UNION ALL
SELECT 3, 'B1 Sentence - sentence' FROM DUAL UNION ALL
SELECT 4, 'B1 Sentence - sentence b' FROM DUAL;
4 rows affected

SELECT CONNECT_BY_ROOT id AS id,
       SUBSTR(
         SYS_CONNECT_BY_PATH(Text, ', '),
         3
       ) AS Text
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
START WITH
       id IN (SELECT id
              FROM   table_name
              WHERE  CONNECT_BY_ISLEAF = 1
              AND    LEVEL = 1
              CONNECT BY NOCYCLE
                     PRIOR text LIKE text || '%'
             )
CONNECT BY NOCYCLE
       text LIKE PRIOR text || '%'


ID
TEXT


1
A1 Text - words, A1 Text - wordsa

3
B1 Sentence - sentence, B1 Sentence - sentence b


SELECT root_id AS id,
       LISTAGG(text, ', ') WITHIN GROUP (ORDER BY ROWNUM) AS text
FROM   (
  SELECT DISTINCT
         CONNECT_BY_ROOT id AS root_id,
         id,
         text
  FROM   table_name
  START WITH
         id IN (SELECT id
                FROM   table_name
                WHERE  CONNECT_BY_ISLEAF = 1
                AND    LEVEL = 1
                CONNECT BY NOCYCLE
                       PRIOR text LIKE text || '%'
               )
  CONNECT BY NOCYCLE
         text LIKE PRIOR text || '%'
  ORDER SIBLINGS BY text
)
GROUP BY root_id


ID
TEXT


1
A1 Text - words, A1 Text - wordsa

3
B1 Sentence - sentence, B1 Sentence - sentence b


SELECT MIN(first_id) AS id,
       LISTAGG(text, ',') WITHIN GROUP (ORDER BY text) AS text
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY text
  MEASURES
    FIRST(id) AS first_id,
    MATCH_NUMBER() AS mno
  ALL ROWS PER MATCH
  PATTERN (matches+)
  DEFINE matches AS (text LIKE FIRST(text) || '%')
)
GROUP BY mno


ID
TEXT


1
A1 Text - words,A1 Text - wordsa

3
B1 Sentence - sentence,B1 Sentence - sentence b


INSERT INTO table_name (id, text)
SELECT 5, 'AAA' FROM DUAL UNION ALL
SELECT 6, 'AAA - BBB' FROM DUAL UNION ALL
SELECT 7, 'AAA - BBB - CCC' FROM DUAL UNION ALL
SELECT 8, 'AAA - BBB - DDD' FROM DUAL UNION ALL
SELECT 9, 'AAA - CCC' FROM DUAL
5 rows affected

SELECT CONNECT_BY_ROOT id AS id,
       SUBSTR(
         SYS_CONNECT_BY_PATH(Text, ', '),
         3
       ) AS Text
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
START WITH
       id IN (SELECT id
              FROM   table_name
              WHERE  CONNECT_BY_ISLEAF = 1
              AND    LEVEL = 1
              CONNECT BY NOCYCLE
                     PRIOR text LIKE text || '%'
             )
CONNECT BY NOCYCLE
       text LIKE PRIOR text || '%'


ID
TEXT


1
A1 Text - words, A1 Text - wordsa

3
B1 Sentence - sentence, B1 Sentence - sentence b

5
AAA, AAA - BBB, AAA - BBB - CCC

5
AAA, AAA - BBB, AAA - BBB - DDD

5
AAA, AAA - BBB - CCC

5
AAA, AAA - BBB - DDD

5
AAA, AAA - CCC


SELECT root_id AS id,
       LISTAGG(text, ', ') WITHIN GROUP (ORDER BY ROWNUM) AS text
FROM   (
  SELECT DISTINCT
         CONNECT_BY_ROOT id AS root_id,
         id,
         text
  FROM   table_name
  START WITH
         id IN (SELECT id
                FROM   table_name
                WHERE  CONNECT_BY_ISLEAF = 1
                AND    LEVEL = 1
                CONNECT BY NOCYCLE
                       PRIOR text LIKE text || '%'
               )
  CONNECT BY NOCYCLE
         text LIKE PRIOR text || '%'
  ORDER SIBLINGS BY text
)
GROUP BY root_id


ID
TEXT


1
A1 Text - words, A1 Text - wordsa

3
B1 Sentence - sentence, B1 Sentence - sentence b

5
AAA, AAA - BBB, AAA - BBB - CCC, AAA - BBB - DDD, AAA - CCC


SELECT MIN(first_id) AS id,
       LISTAGG(text, ', ') WITHIN GROUP (ORDER BY text) AS text
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY text
  MEASURES
    FIRST(id) AS first_id,
    MATCH_NUMBER() AS mno
  ALL ROWS PER MATCH
  PATTERN (matches+)
  DEFINE matches AS (text LIKE FIRST(text) || '%')
)
GROUP BY mno


ID
TEXT


1
A1 Text - words, A1 Text - wordsa

5
AAA, AAA - BBB, AAA - BBB - CCC, AAA - BBB - DDD, AAA - CCC

3
B1 Sentence - sentence, B1 Sentence - sentence b

ID	TEXT
1	A1 Text - words, A1 Text - wordsa
3	B1 Sentence - sentence, B1 Sentence - sentence b

ID	TEXT
1	A1 Text - words,A1 Text - wordsa
3	B1 Sentence - sentence,B1 Sentence - sentence b

ID	TEXT
1	A1 Text - words, A1 Text - wordsa
3	B1 Sentence - sentence, B1 Sentence - sentence b
5	AAA, AAA - BBB, AAA - BBB - CCC
5	AAA, AAA - BBB, AAA - BBB - DDD
5	AAA, AAA - BBB - CCC
5	AAA, AAA - BBB - DDD
5	AAA, AAA - CCC

ID	TEXT
1	A1 Text - words, A1 Text - wordsa
3	B1 Sentence - sentence, B1 Sentence - sentence b
5	AAA, AAA - BBB, AAA - BBB - CCC, AAA - BBB - DDD, AAA - CCC