CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsaggregate
Guilherme Miranda
Guilherme Miranda

Reputation: 1082

Aggregate two columns and rows into one

I have the following table structure 

start|end
09:00|11:00
13:00|14:00

I know

SELECT ARRAY_AGG(start), ARRAY_AGG(end)

Will result in

start|end
[09:00,13:00]|[11:00,14:00]

But how can i get the following result? result

[09:00,11:00,13:00,14:00]

BTW, I'm using Postgres

Upvotes: 5

Views: 2034

Answers (3)

Gabriel's Messanger
Gabriel's Messanger

Reputation: 3298

You could do array concatenation (if order is not important):

SELECT ARRAY_AGG(start) || ARRAY_AGG(end) FROM TABLE1

If order is important you could use Gordon's approach but:

  • add aggregate order array_agg(d order by d ASC)

  • use unnest instead of union all, because Gordon's solution (union all) performs two sequence scan. If table is big it could be better for performance to use:

    SELECT array_agg(d ORDER BY d ASC) FROM(
    SELECT unnest(ARRAY[start] || ARRAY[end]) as d from table1
) sub

which performs only one sequence scan on table (and will be faster).

Upvotes: 5

Vivek S.
Vivek S.

Reputation: 21885

I assume the start and end are character type

select ARRAY_AGG(col) 
from(select string_agg(strt::text||','||en::text,',') col 
     from b
)t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269453

One method is to unpivot them and then aggregate:

select array_agg(d)
from (select start as d from t
      union all
      select end as d from t
     ) t;

A similar method uses a cross join:

select array_agg(case when n.n = 1 then t.start else t.end end)
from t cross join
     (select 1 as n union all select 2) n;

Upvotes: 1

Related Questions