Question 89969 in PostgreSQL Question posted in
The official documentation can be found here.

how do i string_agg these accounts together? – Postgresql

i’ve got a table that’s kinda like this

id account
111111 333-333-2
111111 333-333-1
222222 444-444-1
222222 555-555-1
222222 555-555-2

and i’m trying to aggregate everything up to look like this

id account
111111 333-333-1, -2
222222 444-444-1, 555-555-1, -2

so far i’ve got this

SELECT 
id, 
CONCAT((STRING_AGG(DISTINCT SUBSTRING(account FROM '^(([^-]*-){2})'), ', ')), 
       (STRING_AGG(DISTINCT SUBSTRING(account FROM '[^-]*$'), ', '))) account
GROUP BY id

but this produces

id account
111111 333-333-1, 2
222222 444-444-, 555-555-1, 2
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    , A AS (
SELECT id,
                SUBSTRING(account FROM '^(([^-]*-){2})') first_account,
                STRING_AGG(DISTINCT SUBSTRING(account FROM '[^-]*$'), ', ') second_account
FROM table         
GROUP BY id, first_account
)
select id, STRING_AGG(DISTINCT first_account || second_account, ', ')
FROM A
GROUP BY id

    i ended up figuring it out and this worked for me :))


  2. 0

    I would suggest a different approach: first split the account numbers into main part and suffix, then do separate grouping operations on them:

    SELECT
  id,
  string_agg(accounts, ', ') AS account
FROM (
  SELECT
    id,
    concat(account_main, string_agg(account_suffix, ', ')) AS accounts
  FROM (
    SELECT
      id,
      substr(account, 1, 7) AS account_main,
      substr(account, 8, 9) AS account_suffix
    FROM
      example
  ) AS t1
  GROUP BY
    id,
    account_main
) AS t2
GROUP BY
  id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search