0

This is the sample data that I've got

id program Community ID sdg
246 #NoWasteGiving2020 97 11
246 #NoWasteGiving2020 97 17
246 #NoWasteGiving2020 97 10

With the data on the table above, how do I get the data to just be shown in one row and the sdg shown in one row. so at the end of the day the data will look like this:

246   #NoWasteGiving2020  97  10,11,17

This is the query that I've wrote

select Distinct a.id, a.title as program, b.community_id, c.goal_id as sdg
from programs_program as a
join associates_programcommunity as b on a.id = b.program_id
join associates_communitygoal as c on b.community_id = c.community_id
Kennardy Kusuma
  • 1
  • 1
  • If this is a SQL question, at least share the SQL query you are using. – Evert Feb 02 '21 at 05:49
  • Please tag your question with the database that you are using – Popeye Feb 02 '21 at 05:54
  • Please don't use an image for data, show us as formatted text. – Dale K Feb 02 '21 at 05:57
  • 1
    Please, provide the sample data and desired output. It is not clear what should be done, because by specifying `column_name as sdg` you already have them in one row. – astentx Feb 02 '21 at 06:00
  • i tried using the string_agg function but it gave me this error message function array_agg(integer, unknown) does not exist LINE 1: select a.id, a.title as program, b.community_id, array_agg(c... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. – Kennardy Kusuma Feb 02 '21 at 11:20
  • got it working. I needed to do this: string_agg(c.goal_id::character varying, ', ') as sdg. Thank you everyone! – Kennardy Kusuma Feb 02 '21 at 12:01

1 Answers1

0

You can use string_agg as follows:

select a.id, a.title as program, b.community_id, string_agg(c.goal_id, ',') as sdg
from programs_program as a
join associates_programcommunity as b on a.id = b.program_id
join associates_communitygoal as c on b.community_id = c.community_id
group by a.id, a.title, b.community_id
Popeye
  • 35,427
  • 4
  • 10
  • 31