CODEWITHSUNDEEP
sqloracle-databasejoinlistagg
John Doe
John Doe

Reputation: 319

Using LISTAGG function with join tablee

I want to use LISTAGG() function to aggregate NAME column in my SQL query. I have to join 3 tables. Table PRI2PR is my join-table.

Table PROJ_INW

        ID
----------
        12

Table PROJEKT

ID     NAME
---  ---------------
61    Józef Dawidowski

Table PRI2PR

ID    ID_PRIN    ID_PROJ 
---------- ---------- ---
9         12         61

My query:

SELECT pi.ID,
LISTAGG(pr.NAME, ',') WITHIN GROUP (ORDER BY pr.NAME) AS PRO
FROM PROJ_INW pi
INNER JOIN PRI2PR pp ON pi.ID = pp.ID_PRIN
INNER JOIN PROJEKT pr ON pp.ID_PROJ = pr.ID
GROUP BY pi.ID, pr.NAME

Question: How to correct my SQL statement to use LISTAGG() function in my case?

Upvotes: 0

Views: 2041

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your query is basically fine. Remove pr.name from the group by:

SELECT pi.ID,
       LISTAGG(pr.NAME, ',') WITHIN GROUP (ORDER BY pr.NAME) AS PRO
FROM PROJ_INW pi INNER JOIN
     PRI2PR pp
     ON pi.ID = pp.ID_PRIN INNER JOIN
     PROJEKT pr
     ON pp.ID_PROJ = pr.ID
GROUP BY pi.ID;

Upvotes: 3

Related Questions