Oracle SQL - combining values into one row

Question

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.

Any suggestions would be appreciated.

SqlZim · Accepted Answer · 2017-05-12 20:53:37Z

Remove TABLE.HOLD from your group by.

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

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 | +-----+--------+------------+

Gordon Linoff · Accepted Answer · 2017-05-12 20:54:16Z

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

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.

Collectives™ on Stack Overflow

Oracle SQL - combining values into one row

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related