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

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

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

CodePudding user response：

, 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 :))

CodePudding user response：

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;