以逗號分隔的單列中顯示子記錄，並與父記錄分隔

為 Oracle 提供如下所示的父子表

T_OPT_PARENT

OPT | OPT_DESC| STATUS 
XY  | XY_DESC | A 
YZ  | YZ_DESC | A 
ZZ  | ZZ_DESC | A 

T_OPT_CHILD

OPT | CODE
XY  | ONE_CODE
XY  | TWO_CODE
XY  | THREE_CODE
ZZ  | ONE_CODE
ZZ  | TWO_CODE

預期產出

OPT | OPT_DESC| CODE                 | STATUS 
XY  | XY_DESC | ONE_CODE,TWO_CODE,THREE_CODE | A
YZ  | YZ_DESC |                              | A 
ZZ  | ZZ_DESC | ONE_CODE, TWO_CODE           | A 

有人可以幫助我建構 SQL 以獲得預期的結果。

設置表格和數據：

create table T_OPT_PARENT
(OPT       char(2)
,OPT_DESC  varchar2(10)
,STATUS    char(1));

insert into T_OPT_PARENT
select 'XY','XY_DESC','A' from dual union all
select 'YZ','YZ_DESC','A' from dual union all
select 'ZZ','ZZ_DESC','A' from dual;

create table T_OPT_CHILD
(OPT       char(2)
,CODE      varchar2(10));

insert into T_OPT_CHILD
select 'XY','ONE_CODE'   from dual union all
select 'XY','TWO_CODE'   from dual union all
select 'XY','THREE_CODE' from dual union all
select 'ZZ','ONE_CODE'   from dual union all
select 'ZZ','TWO_CODE'   from dual;

這是一個基於 aleft join和listagg()函式的解決方案：

select p.OPT,
      p.OPT_DESC,
      listagg(c.CODE, ', ') within group (order by ROWNUM) "CODE",
      p.STATUS

from T_OPT_PARENT p

left
join T_OPT_CHILD c
on   c.OPT = p.OPT

group by p.OPT, 
        p.OPT_DESC,
        p.STATUS

order by 1;

OPT | OPT_DESC | CODE                           | STATUS
--- | -------- | ------------------------------ | ------
XY  | XY_DESC  | ONE_CODE, TWO_CODE, THREE_CODE | A     
YZ  | YZ_DESC  | null                           | A     
ZZ  | ZZ_DESC  | ONE_CODE, TWO_CODE             | A  

注意：我能夠根據需要對XYCODE進行排序，ROWNUM 只是T_OPT_CHILD因為我以與所需排序順序相同的順序將記錄插入到表中。的使用ROWNUM可能不適用於您的真實數據，因此您可能需要相應地修改該order by ROWNUM子句。

這是一個dbfiddle

引用自：https://dba.stackexchange.com/questions/192812