Retrieve data from the same column in two columns

user_6265779 0
Viewed 55

I have a table in PostgreSQL, something like this:

ID      NAME
450     China
525     Germany
658     Austria

I’d like to query every names where ID < 500 and at the same time where ID > 500 and retrieve the result in two columns using 

array_to_string(array_agg(NAME),  ',  ').

I need the following result:

column1 (ID < 500)      column2 (ID > 500)
China                   Germany, Austria
postgresql
2 Answers

Something like:

select (select string_agg(name, ', ') 
        from the_table
        where id <= 500) as column1, 
       (select string_agg(name, ', ') 
        from the_table
        where id > 500) as column2;

Alternatively:

select string_agg(name, ', ') filter (where id <= 500) as column1, 
       string_agg(name, ', ') filter (where id > 500) as column2
from the_table;
user_3303150

Try using conditional aggregation:

SELECT
    STRING_AGG(CASE WHEN ID < 500 THEN NAME END, ', ')  AS ID_lt_500,
    STRING_AGG(CASE WHEN ID >= 500 THEN NAME END, ', ') AS ID_gt_500
FROM yourTable;

enter image description here

Demo

Edit:

If you are using a version of Postgres which does not support STRING_AGG, then do as you were already doing:

SELECT
    ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN ID < 500 THEN NAME END), ', ')  AS ID_lt_500,
    ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN ID >= 500 THEN NAME END), ', ')  AS ID_gt_500
FROM yourTable;

Demo

user_18632290
Related