Oracle SQL - 将值组合成一行

My table has multiple values per person, which I'm trying to combine into one row. Here's the query:

我的表有每个人的多个值，我正在尝试将其组合成一行。这是查询：

select TABLE.ID,
        TABLE.NAME,
        listagg(TABLE.HOLD, ',') within group (order by TABLE.HOLD) as hold_codes
   from TABLE
   where TABLE.ACTIVE_HOLD_IND ='Y'
        and TABLE.HOLD in('S2', 'S3', 'CO', 'WO', 'PP')
  group by
 TABLE.ID,
 TABLE.NAME,
 TABLE.HOLD
 order by 2

ID  |NAME   |HOLD_CODES
_____________________________
111 |Tom    |S2
222 |Jerry  |CO
222 |Jerry  |S2
333 |Olive  |S2,S2
444 |Popeye |CO
444 |Popeye |PP
444 |Popeye |S2
555 |Homer  |S2,S2
666 |Marge  |S2

I'm trying to combine each ID on one line. Right now, the query only picks up the rows of duplicates.

我正在尝试将每个ID组合在一行上。现在，查询只会获取重复行。

Any suggestions would be appreciated.

任何建议，将不胜感激。

2 个解决方案

#1

Remove TABLE.HOLD from your group by.

从您的组中删除TABLE.HOLD。

with cte as (
          select 111 as id,'Tom   ' as name,'S2' as hold from dual
union all select 222,'Jerry ','CO' from dual
union all select 222,'Jerry ','S2' from dual
union all select 333,'Olive ','S2' from dual
union all select 444,'Popeye','CO' from dual
union all select 444,'Popeye','PP' from dual
union all select 444,'Popeye','S2' from dual
union all select 555,'Homer ','S2' from dual
union all select 666,'Marge ','S2' from dual
)
select 
    cte.ID
  , cte.name
  , listagg(cte.HOLD, ',') within group (order by cte.HOLD) as hold_codes
from cte
where cte.HOLD in ('S2', 'S3', 'CO', 'WO', 'PP')
group by cte.ID
 , cte.name
order by 2

rextester demo: http://rextester.com/FPFI26814

rextester演示：http：//rextester.com/FPFI26814

returns:

收益：

+-----+--------+------------+
| ID  |  NAME  | HOLD_CODES |
+-----+--------+------------+
| 555 | Homer  | S2         |
| 222 | Jerry  | CO,S2      |
| 666 | Marge  | S2         |
| 333 | Olive  | S2         |
| 444 | Popeye | CO,PP,S2   |
| 111 | Tom    | S2         |
+-----+--------+------------+

#2

Is id unique per row or per person? The following is safe regardless:

每行或每人的ID是唯一的吗？以下是安全的，无论如何：

select t.NAME,
        listagg(t.HOLD, ',') within group (order by t.HOLD) as hold_codes
from TABLE t
where t.ACTIVE_HOLD_IND = 'Y' and
      t.HOLD in('S2', 'S3', 'CO', 'WO', 'PP')
group by t.NAME
order by NAME;

Obviously, HOLD needs to be removed the from GROUP BY, but ID might also need to be removed as well.

显然，需要从GROUP BY中删除HOLD，但也可能需要删除ID。

#1