CODEWITHSUNDEEP
sqlplsql
Awais
Awais

Reputation: 67

Need to condence data in oracle table

create table temp_1 (part_number number , application varchar2)
insert into  temp_1 values (10,1997-2004 ford)
insert into  temp_1 values (10,1997-2004 mustand)

select * from temp_1
10 1997-2004 ford
10 1997-2004 mustand

i want the data to be display like this 

part number  application 
10,         1997-2004 ford,mustang

so far i have tried 

select LISTAGG(application,'~') WITHIN GROUP (order by part_number)  from temp_1

but not getting the desired result

Upvotes: 1

Views: 58

Answers (1)

Dba
Dba

Reputation: 6639

To get this done, You need to split the values in application field to YEAR and MAKE. Here I've split it using SUBSTR().

Assuming that the Year will always be in a fixed format YYYY-YYYY.

SELECT part_number, 
       YEAR|| listagg(make, ',') within group (order by part_number) application
FROM(
     SELECT part_number, 
            substr(application, 1, 9) YEAR, 
            substr(application, 10, LENGTH(application)) make
     FROM   temp_1
     )
GROUP BY part_number, year;

Upvotes: 3

Related Questions