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
Let’s assume you do not perform STRING_AGG() in the query. In this case the output looks like
and you can use conditional aggregation as in the query below
in case when number of approvers is undefined,please google "PostgreSQL dynamic pivot"
DATA
use
Subquery
,string_agg
andSPLIT_PART
dbfiddle
or in your query,note that changing
', '
to','
A simpler option is to use
array_agg