CODEWITHSUNDEEP
sqloracle-databaseplsqlconcatenationstring-concatenation
Jans
Jans

Reputation: 11250

Oracle Concatenate Column value in a single row

Let's say I have a N:N table relationship, and i'm doing a join between them, the problem is that the result have many rows with the same values, and i want to resume in one row containing the ids of those.
The result that actually returned look like this. 

    ID_STREET     STREET      COUNTRY       REGION    
    ------------------------------------------------
    1             street-1    country-1     region-1     
    2             street-1    country-1     region-1
    3             street-2    country-2     region-2

and i want to get it as 

        ID_STREET     STREET      COUNTRY       REGION   DUPLICATED 
    ---------------------------------------------------------------
    1             street-1    country-1     region-1     1,2
    3             street-2    country-2     region-2     3

Table definition look like 

-------------------------         --------------------------
        STREET                            COUNTRY
-------------------------         --------------------------
ID   NUMBER NOT NULL              ID   NUMBER NOT NULL
NAME VARCHAR2(45) NOT NULL        NAME VARCHAR2(45) NOT NULL
ID_COUNTRY NUMBER NOT NULL         ID_REGION NUMBER NOT NULL
...other fields                   ...other fields

-------------------------         
        REGION                            
-------------------------         
ID   NUMBER NOT NULL              
NAME VARCHAR2(45) NOT NULL        
...other fields

Upvotes: 0

Views: 5174

Answers (1)

N West
N West

Reputation: 6819

On oracle, the aggregate LISTAGG function can do string concatenation on a column grouped by other columns.

http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm

From what I can gather from your table, if we did this to your first set of results

SELECT MIN(ID_STREET), STREET, COUNTRY, REGION, 
       LISTAGG(ID_STREET, ',') WITHIN GROUP (ORDER BY STREET) AS DUPLICATED
  FROM MYTABLE
GROUP BY STREET,COUNTRY,REGION

We'd end up with your second. I would need to see the original tables to show a query that would work to do what you want to do.

Upvotes: 2

Related Questions