How to concatenate strings of a string field in a PostgreSQL 'WITH RECURSIVE' query?

Question

As a follow up to this question How to concatenate strings of a string field in a PostgreSQL 'group by' query?

I am looking for a way to concatenate the strings of a field within a WITH RECURSIVE query (and NOT using GORUP BY). So for example, I have a table:

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave
5    3            Tom

and I wanted to group by company_id, ordered by the count of EMPLOYEE, to get something like:

COMPANY_ID   EMPLOYEE
3            Tom
1            Anna, Bill
2            Carol, Dave

Why would you *not* use the simpler `GROUP BY` for the task? Also, please lead with your version of Postgres and an exact table definition (`CREATE TABLE` statement) for any such question. Can there be duplicate names? NULL values? Details matter. — Erwin Brandstetter, Jun 06 '22 at 23:43

score 2 · Answer 1 · answered Jun 07 '22 at 00:24

It's simple with GROUP BY:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee) AS employees
FROM   tbl
GROUP  BY company_id
ORDER  BY count(*), company_id;

Sorting in a subquery is typically faster:

SELECT company_id, string_agg(employee, ', ') AS employees
FROM  (SELECT company_id, employee FROM tbl ORDER BY 1, 2) t
GROUP  BY company_id
ORDER  BY count(*), company_id;

As academic proof of concept: an rCTE solution without using any aggregate or window functions:

WITH RECURSIVE rcte AS (
   (
   SELECT DISTINCT ON (1)
          company_id, employee, ARRAY[employee] AS employees
   FROM   tbl
   ORDER  BY 1, 2
   )
   UNION ALL
   SELECT r.company_id, e.employee, r.employees || e.employee
   FROM   rcte r
   CROSS  JOIN LATERAL (
      SELECT t.employee
      FROM   tbl t
      WHERE  t.company_id = r.company_id
      AND    t.employee   > r.employee
      ORDER  BY t.employee
      LIMIT  1
      ) e
   )
SELECT company_id, array_to_string(employees, ', ') AS employees
FROM  (
   SELECT DISTINCT ON (1)
          company_id, cardinality(employees) AS emp_ct, employees
   FROM   rcte
   ORDER  BY 1, 2 DESC
   ) sub
ORDER  BY emp_ct, company_id;

db<>fiddle here

score 1 · Answer 2 · answered Jun 06 '22 at 22:31

No group by here:

select * from tarded;
┌────┬────────────┬──────────┐
│ id │ company_id │ employee │
├────┼────────────┼──────────┤
│  1 │          1 │ Anna     │
│  2 │          1 │ Bill     │
│  3 │          2 │ Carol    │
│  4 │          2 │ Dave     │
│  5 │          3 │ Tom      │
└────┴────────────┴──────────┘
(5 rows)

with recursive firsts as (
  select id, company_id, 
         first_value(id) over w as first_id, 
         row_number() over w as rn,
         count(1) over (partition by company_id) as ncompany,
         employee
    from tarded
  window w as (partition by company_id 
                   order by id)
), names  as (
  select company_id, id, employee, rn, ncompany
    from firsts 
   where id = first_id
  union all
  select p.company_id, c.id, concat(p.employee, ', ', c.employee), c.rn, p.ncompany
    from names p
         join firsts c
           on c.company_id = p.company_id
          and c.rn = p.rn + 1
)
select company_id, employee 
  from names
 where rn = ncompany
 order by ncompany, company_id;
┌────────────┬─────────────┐
│ company_id │  employee   │
├────────────┼─────────────┤
│          3 │ Tom         │
│          1 │ Anna, Bill  │
│          2 │ Carol, Dave │
└────────────┴─────────────┘
(3 rows)

this query is better than Erwin's answers. because it allow employee to have duplicated value. — jian, Dec 30 '22 at 07:57

How to concatenate strings of a string field in a PostgreSQL 'WITH RECURSIVE' query?

2 Answers2