How to split two strings separated by a ; in two new columns-CodePudding

So I have a command where for example

SELECT something, string_agg(other, ';') FROM table
GROUP BY something HAVING COUNT(*)>1;

but I don't know how to separate in two columns, because it doesn't see string_agg as a column.

This is my original

something | other |         
-------- -------- 
 example  | yes, no   |  
 using  | why, what  |

and I would like this please

something | other | new        
-------- -------- ------
 example  | yes   | no     
 using  | why     | what

CodePudding user response：

We can use regular expressions here:

SELECT
    something,
    SUBSTRING(other FROM '[^,] ') AS other,
    REGEXP_REPLACE(other, '.*,[ ]*', '') AS new
FROM yourTable;

CodePudding user response：

I would aggregate this into an array:

select something, 
       others[1] as other, 
       others[2] as "new"
from (
  SELECT something, array_agg(other) as others
  FROM table
  GROUP BY something 
  HAVING COUNT(*)>1
) x