CODEWITHSUNDEEP
sqloracle-databaseoracle11gstring-aggregation
738560
738560

Reputation: 129

Categorize data using sql

I have data in the below format in status table (newstat) with unique identifer id

  

 id status status1
----- 
 1 COMP ACC REQ 
 2 COMP DECL 
 3 ACR  ACC REQ 
 4 ACR  CANCEL 
 5 ACW     DECL  
 6 ACW     ACC REQ

.....

using sql query (oracle 11g) i need the data to be in nested format

  
 COMP    ACC REQ, DECL
 ACR     ACC REQ, CANCEL
 ACW     ACC REQ, DECL

i have tried code using LISTAGG(), wm_concat

tried using sql but all are generating errors.

ORA-00904: "WM_CONCAT": invalid identifier

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

Doesn't this work?

select status, listagg(status1, ', ') within group (order by id) as statuses
from t
group by status
order by min(id);

Upvotes: 1

Related Questions