skip to Main Content

I am using Postgres, and would like to write SQL to do the following.

I have this so far:

select a.id, string_agg(concat(c.fname), ', ') as approvers 
from
autoapprovalapproverconfig a
left join autoapprovalapproverconfigmandate a2 on a2.autoapprovalaconfigurationid = a.id
left join person p on p.personid = a2.approverid
left join person_contact pc on pc.person_personid = p.personid
left join public.clientcontact c on c.contact_id = pc.personaldetails_contact_id
group by a.id

Which returns:

ID     APPROVERS
--     ---------
1     nameA, nameB
2     nameC
3     nameD, nameE, nameF
4     nameG
5     nameH, nameI

How do I make the SQL rather return: (remove the aggregate function and rather show each in a separate column)

ID     APPROVER1   APPROVER3    APPROVER3
--     ---------   ---------    ---------
1        nameA       nameB
2        nameC
3        nameD       nameE        nameF
4        nameG
5        nameH       nameI

Where there is a max of 3 approvers shown per row.

3

Answers


  1. Let’s assume you do not perform STRING_AGG() in the query. In this case the output looks like

     WITH CTE(ID,APPROVER)AS
     (
        SELECT 1,'nameA' UNION ALL
        SELECT 1,'nameB' UNION ALL
        SELECT 2,'nameC' UNION ALL
        SELECT 3,'nameD' UNION ALL
        SELECT 3,'nameE' UNION ALL
        SELECT 3,'nameF' UNION ALL
        SELECT 4,'nameG' UNION ALL
        SELECT 5,'nameH' UNION ALL
        SELECT 5,'nameI'
    )
    

    and you can use conditional aggregation as in the query below

    SELECT XX.ID,
    MAX(
         CASE 
           WHEN XX.XCOL=1 THEN XX.APPROVER
           ELSE ''
         END   
       )AS APPROVER_1,
    MAX(
        CASE 
         WHEN XX.XCOL=2 THEN XX.APPROVER
           ELSE ''
        END   
       )AS APPROVER_2,
     MAX(
         CASE 
           WHEN XX.XCOL=3 THEN XX.APPROVER
           ELSE ''
          END   
       )AS APPROVER_3
     FROM
      ( 
           SELECT C.ID,C.APPROVER,
          ROW_NUMBER()OVER(PARTITION BY C.ID ORDER BY C.APPROVER ASC)XCOL
         FROM CTE AS C 
      )XX
     GROUP BY XX.ID
    

    in case when number of approvers is undefined,please google "PostgreSQL dynamic pivot"

    Login or Signup to reply.
  2. DATA

    CREATE TABLE TEST(ID INT,fname varchar(100));
    INSERT INTO TEST
    (ID,fname) VALUES
    (1 ,'nameA' ),
    ( 1,'nameB'),
    ( 2,'nameC'),
    ( 3,'nameD'),
    ( 3,'nameE'),
    ( 3,'nameF'),
    ( 4,'nameG'),
    ( 5,'nameH'),
    ( 5,'nameI');
    

    use Subquery, string_agg and SPLIT_PART

    SELECT 
    ID,
    SPLIT_PART(approvers, ',', 1) APPROVERS1,
    SPLIT_PART(approvers, ',', 2) APPROVERS2,
    SPLIT_PART(approvers, ',', 3) APPROVERS3
      FROM (
    SELECT id, string_agg(concat(fname), ',') as APPROVERS 
    FROM TEST
    group by  id) A
    ORDER BY ID
    

    dbfiddle

    or in your query,note that changing ', ' to ','

    SELECT 
    ID,
    SPLIT_PART(approvers, ',', 1) APPROVERS1,
    SPLIT_PART(approvers, ',', 2) APPROVERS2,
    SPLIT_PART(approvers, ',', 3) APPROVERS3
      FROM (
        select a.id, string_agg(concat(c.fname), ',') as approvers 
        from
        autoapprovalapproverconfig a
        left join autoapprovalapproverconfigmandate a2 on a2.autoapprovalaconfigurationid = a.id
        left join person p on p.personid = a2.approverid
        left join person_contact pc on pc.person_personid = p.personid
        left join public.clientcontact c on c.contact_id = pc.personaldetails_contact_id
        group by a.id) A
    ORDER BY ID
    
    Login or Signup to reply.
  3. A simpler option is to use array_agg

    select
      a.id,
      array_agg(c.fname)[1] as approver1,
      array_agg(c.fname)[2] as approver2,
      array_agg(c.fname)[3] as approver3
    from autoapprovalapproverconfig a
    left join autoapprovalapproverconfigmandate a2 on a2.autoapprovalaconfigurationid = a.id
    left join person p on p.personid = a2.approverid
    left join person_contact pc on pc.person_personid = p.personid
    left join public.clientcontact c on c.contact_id = pc.personaldetails_contact_id
    group by
      a.id;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search