Combine multiple values into one cell in Postgres

Question

I have a data set that can multiple rows with the same claim no. and different names:

   claim_id           name
    112               John Smith
    112               Tom Harris
    113               Randy Dugar

If the claim id is the same, then I need all the names to be in one row separated by a comma:

    claim_no          name
    112               John Smith, Tom Harris
    113               Randy Dugar

I've created something similar in T-SQL, but am new to Postgres. I've tried something like the following, but I just get an error:

   with firstrun as(
   select distinct kia.claim_id as claim_id,  c.first_name ||' '  
   ||c.last_name as name
   from kia
   inner join
   claims c
   on kia.claim_id = c.claim_id


   )

   select distinct claim_id, substring((Select ',' || ' '|| fr.name as   text()]
   from firstrun fr
   for xml path('')), 2, 500)

Hint: string_agg().

Gordon Linoff
– Gordon Linoff

2018-09-27 20:35:23 +00:00
Commented Sep 27, 2018 at 20:35 — Gordon Linoff
– Gordon Linoff, Commented Sep 27, 2018 at 20:35

Mureinik · Accepted Answer · 2018-09-27 20:37:15Z

4

The string_agg aggregate function will do all the heavy lifting for you:

SELECT   claim_id, STRING_AGG(name, ', ')
FROM     claims
GROUP BY claim_id

answered Sep 27, 2018 at 20:37

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jackstraw22 Over a year ago

This is great. Never heard of that function before and it works perfectly.

Collectives™ on Stack Overflow

Combine multiple values into one cell in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related