postgresql how to join multiple tables to get comma separated output

Question

I have following tables:

table1 id, user_id, first_name, last_name
table2 id, user_id, company_name, first_name, last_name, partner_id
table3 businesses id, business_name

I want to create query which will return rows of data: user_id, (first_name last_name), (business_name, business_name ...) When user is associated with multiple businesses, something like this: 123, John Doe, Business1, Business2

I can get kind of duplicate rows when I use following query:

SELECT table1.first_name, table1.last_name, table2.copmany_name,
       (case when concat(table1.first_name, table1.last_name) = '' then table2.company_name else concat_ws(' ', table1.first_name,table1.last_name) end) as name,
       table3.name as business_name
FROM table1
JOIN table2 ON table1.user_id = table2.user_id
JOIN table3 ON table2.partner_id = table3.id

Here is sample from that query:

123, John Doe, Business1
123, John Doe, Business2
125, Marie Bird, Business3

And I want to get:

123, John Doe, Business1, Business2
125, Marie Bird, Business3

Is that possible?

Thank you

To a file or just to the query results? If the former, check out http://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file. If the latter, look up the concatenation operator `||` — Hambone, Dec 28 '15 at 20:44
Just to get query results. My problem is how to concatenate those 2 or more rows related to the same user_id — lradin, Dec 28 '15 at 20:47
Aah, I see now... I didn't do a good job of reading your question. — Hambone, Dec 28 '15 at 20:48
Do you need to see it exactly the way you listed it (as pure CSV), or is notionally fine to have the actual output vary, provided you get multiple rows of data in a single row, the way you have outlined? — Hambone, Dec 28 '15 at 20:55

score 0 · Answer 1 · answered Dec 28 '15 at 20:52

You can use array_agg in postgresql.Aggregate functions compute a single result from a set of input values.:

SELECT table1.first_name, table1.last_name, table2.copmany_name,
       (case when concat(table1.first_name, table1.last_name) = '' then table2.company_name else concat_ws(' ', table1.first_name,table1.last_name) end) as name,
       array_agg(table3.name) as business_name
FROM table1
JOIN table2 ON table1.user_id = table2.user_id
JOIN table3 ON table2.partner_id = table3.id

score 0 · Answer 2 · answered Dec 28 '15 at 20:56

The string_agg aggregate function allows you to concatinate expression together:

SELECT   table1.first_name,
         table1.last_name,
         table2.copmany_name,
         (CASE WHEN CONCAT(table1.first_name, table1.last_name) = '' 
               THEN table2.company_name 
               ELSE CONCAT_WS(' ', table1.first_name, table1.last_name) 
          END) AS name,
         STRING_AGG(table3.name, ', ') AS business_name
FROM     table1
JOIN     table2 ON table1.user_id = table2.user_id
JOIN     table3 ON table2.partner_id = table3.id
GROUP BY table1.first_name,
         table1.last_name,
         table2.copmany_name,
         (CASE WHEN CONCAT(table1.first_name, table1.last_name) = '' 
               THEN table2.company_name 
               ELSE CONCAT_WS(' ', table1.first_name, table1.last_name) 
          END) AS name

thank you for the idea to use aggregate function. However this particular example does not work the way I need. 1st if aggregate based on first_name, last_name combination not on user_id, 2nd, if I add to select and group by table1.user_id I am getting the same results as my original query — lradin, Dec 28 '15 at 21:23

postgresql how to join multiple tables to get comma separated output

2 Answers2