Как в Oracle получить все значения поля в одной строке

Возможны следующие следующие варианты решения:

1. С помощью xmlagg:

with t as (
    select 1 as id, 'aaa' as str from dual union all
    select 2 as id, 'bbb' as str from dual union all
    select 3 as id, 'ccc' as str from dual
)
SELECT xmlagg(xmlelement("node", t.str, ', ')).extract('//text()').getStringVal() as result1
     , rtrim(xmlagg(xmlelement("node", t.str, ', ').extract('//text()')),', ') as result2
FROM t;


RESULT1              RESULT2
------------------   ------------------
aaa, bbb, ccc,       aaa, bbb, ccc

2. Через sys_connect_by_path:

with t as (
    select 1 as id, 'aaa' as str from dual union all
    select 2 as id, 'bbb' as str from dual union all
    select 3 as id, 'ccc' as str from dual 
)
SELECT ltrim(sys_connect_by_path(str, ', '), ', ') as result1
FROM (
     select t.str,
            row_number() over (order by t.str) as id,
            count(*) over (order by t.str ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt
     from t
    )
WHERE id = cnt
START WITH id = 1
CONNECT BY id = PRIOR id + 1;


RESULT1
------------------
aaa, bbb, ccc

Начиная с версии 11.2 появилась функция LISTAGG:

3. С помощью LISTAGG:

with t as (
    select 1 as id, 'aaa' as str from dual union all
    select 2 as id, 'bbb' as str from dual union all
    select 3 as id, 'ccc' as str from dual 
)
SELECT LISTAGG(t.str, ', ') WITHIN GROUP (order by t.id) as result1
FROM t;


RESULT1
------------------
aaa, bbb, ccc

That's about it.