Postgresql - SQL Group By aggregate into separate columns

Richard
May 13, 2024
43 views
2 votes
3 Answers

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.

Tags: postgresql sql

Answers

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"

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

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;

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – SQL Group By aggregate into separate columns

Answers