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

1

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.

answered May 12, 2017 at 20:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - combining values into one row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related