postgresql inner join to string join together

Question

I have the below query

SELECT 
CCTP.C5NCCT AS "Numéro",
CCTSPEP.CBCSPE AS "Spécialité"
FROM FIC.CCTP CCTP
LEFT JOIN FIC.CCTSPEP CCTSPEP ON CCTSPEP.CBNCCT = CCTP.C5NCCT;

which returns this result

Numéro	Spécialité
1	01
1	24
1	25
2	02
2	06
3	11

I want to update the query to get a result similar to this

Numéro	Spécialité
1	01, 24, 25
2	02, 06
3	11

Does this answer your question? [What's the equivalent for LISTAGG (Oracle database) in PostgreSQL?](https://stackoverflow.com/questions/29557563/whats-the-equivalent-for-listagg-oracle-database-in-postgresql) — Ankit Bajpai, Sep 13 '22 at 11:18
refer this https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Uday Dodiya, Sep 13 '22 at 11:28

score 0 · Answer 1 · edited Sep 19 '22 at 17:35

0

select 
  distinct CCTP.C5NCCT AS Numéro, 
  (
    SELECT 
      STUFF(
        (
          SELECT 
            ',' + etm.Spécialité AS Spécialité 
          FROM 
            (
              select 
                distinct CCTP.C5NCCT, 
                cast(
                  CCTP.CBCSPE as nvarchar(100)
                ) as Spécialitéfrom FIC.CCTP 
                LEFT JOIN FIC.CCTSPEP CCTSPEP ON CCTSPEP.CBNCCT = CCTP.C5NCCT
            ) etm 
          WHERE 
            etm.C5NCCT = FIC.CCTP.C5NCCT 
          ORDER BY 
            etm.C5NCCT FOR XML PATH('')
        ), 
        1, 
        1, 
        ''
      ) AS expr
  ) Spécialité

edited Sep 19 '22 at 17:35

Siddhartha Mukherjee

2,703
2
24
29

answered Sep 13 '22 at 11:42

irfan multani

36
4

i have this error: `SQL Error [42601]: [SQL0104] Elément syntaxique é n'est pas correct. Eléments possibles : , FROM INTO.` – Aymen Kanzari Sep 13 '22 at 13:41
chake this Replase name table name with select distinct table1.C5NCCT AS Numéro, ( SELECT STUFF( (SELECT ',' + etm.Spécialité AS Spécialité FROM ( select distinct table1.C5NCCT , cast(table1.CBCSPE as nvarchar(100)) as Spécialité from FIC.table1 LEFT JOIN FIC.table2 CCTSPEP ON table2.CBNCCT = table1.C5NCCT ) etm WHERE etm.C5NCCT =FIC.table1.C5NCCT ORDER BY etm.C5NCCT FOR XML PATH('')), 1, 1, '') AS expr ) Spécialité from table1 – irfan multani Sep 15 '22 at 09:58

postgresql inner join to string join together

1 Answers1