CODEWITHSUNDEEP
sqloracle-database
Lyndey
Lyndey

Reputation: 77

Oracle SQL - combining values into one row

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.

Upvotes: 1

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

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

Upvotes: 2

Related Questions