How to parse this code from SQL SERVER to POSTGRESQL?

Question

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';

Tim Biegeleisen · Accepted Answer · 2022-07-02 07:17:08Z

2

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;

answered Jul 2, 2022 at 7:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dilyor Toshboltayev Over a year ago

thansk, fine worked

Collectives™ on Stack Overflow

How to parse this code from SQL SERVER to POSTGRESQL?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related