postgresql inner join to string join together

Aymen Kanzari 0
Viewed 35

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
sql postgresql
1 Answers
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é
irfan multani0
Related