求教 如何将一个字段查询出的多个结果拼接到一起

[瞌觉兴国]

因为要确定一条数据，所以查询出多个结果的话会报错。
所以想问一下 如果将查询出的多个结果拼接到一起

下面的是语句

(select wav.comment_text
from wfl_approve_record_v wav
where wav.instance_id =
(select max(wwi.instance_id)
from wfl_workflow_instance wwi
where wwi.document_number = y.document_number)
and exists (select 1
from wfl_workflow_deliver wwd
where wwd.recipient_id = wav.created_by
and wwd.workflow_id = wav.workflow_id)
and exists (select wwd.workflow_id
from wfl_workflow_deliver wwd
where wwd.deliver_to_id = wav.created_by
and wwd.workflow_id = wav.workflow_id)) text,

[瞌觉兴国]

另外想问一下 我对语句进行排序为什么会报缺失右括号 (select wav.comment_text from wfl_approve_record_v wav where wav.instance_id = (select max(wwi.instance_id) from wfl_workflow_instance wwi where wwi.document_number = y.document_number) and exists (select 1 from wfl_workflow_deliver wwd where wwd.recipient_id = wav.created_by and wwd.workflow_id = wav.workflow_id) and exists (select wwd.workflow_id from wfl_workflow_deliver wwd where wwd.deliver_to_id = wav.created_by and wwd.workflow_id = wav.workflow_id) order by wav.creation_date) text, 如果将最外层的 （）text， 去掉的话是可以运行的。

[bw555]

select wmsys.wm_concat(comment_text) from (你的查询语句)

11g之后可以使用listagg

[bw555]

listagg的好处是可以控制拼接的顺序和连接符

select listagg(comment_text,',') within group(order by creation_date) from (
select wav.comment_text,wav.creation_date
          from wfl_approve_record_v wav
         where wav.instance_id =
               (select max(wwi.instance_id)
                  from wfl_workflow_instance wwi
                 where wwi.document_number = y.document_number)
           and exists (select 1
                  from wfl_workflow_deliver wwd
                 where wwd.recipient_id = wav.created_by
                   and wwd.workflow_id = wav.workflow_id)
           and exists (select wwd.workflow_id
                  from wfl_workflow_deliver wwd
                 where wwd.deliver_to_id = wav.created_by
                   and wwd.workflow_id = wav.workflow_id)
)

[瞌觉兴国]

引用 2 楼 bw555 的回复:

select wmsys.wm_concat(comment_text) from (你的查询语句)

11g之后可以使用listagg

谢谢！