postgresql内部连接到字符串连接在一起

Question

我有以下查询

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;

它返回这个结果

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

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é