How to parse this code from SQL SERVER to POSTGRESQL?

SELECT A.GroupNo
             , A.AuthorityGroupName
             , STUFF((SELECT ', '   C.CodeName
                       FROM TUserAuthority B 
                      INNER JOIN TDetailCode C 
                         ON B.UserType = C.CodeValue
                      WHERE B.DeleteYesNo = 'N'
                        AND B.GroupNo = A.GroupNo
                        FOR XML PATH('')), 1, 2, '') AS UserTypeArray
             , A.UseYesNo
          FROM TAuthorityGroup A 
         WHERE A.DeleteYesNo = 'N';

CodePudding user response：

Your SQL Server query is doing an old style group concatenation. From Postgres 9 we can simply use STRING_AGG:

SELECT a.GroupNo,
       a.AuthorityGroupName,
       a.UseYesNo,
       STRING_AGG(c.CodeName, ', ') AS UserTypeArray
FROM TAuthorityGroup a
LEFT JOIN TUserAuthority b
    ON b.GroupNo = a.GroupNo AND
       b.DeleteYesNo = 'N'
LEFT JOIN TDetailCode c
    ON b.UserType = c.CodeValue
WHERE
    a.DeleteYesNo = 'N'
GROUP BY
    a.GroupNo,
    a.AuthorityGroupName,
    a.UseYesNo;