SQL Group By aggregate into separate columns

May 13, 2024

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.

>Solution :

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