Search code examples
sqlpostgresqlpsql

PSQL query select with multiple grouping

Please help to create correct select query for postgresql. I have a table with next data:

srv table_name heartbeat
server1 table1 10
server1 table2 7
server2 table1 13
server2 table2 23
server3 table1 3
server3 table2 15

I try to create select query that will turn back next result:

table_name server1 server2 server3
table1 10 13 3
table2 7 23 15

Tables names always static (about 20 tables names). But servers names can be changed any time: added or deleted.

Solution

  • This can be done using the conditional aggreagtion :

    select table_name,
       max(case when srv = 'server1' then heartbeat end) as server1,
       max(case when srv = 'server2' then heartbeat end) as server2,
       max(case when srv = 'server3' then heartbeat end) as server3
from mytable
group by table_name

    Result :

    table_name server1 server2 server3
    table1 10 13 3
    table2 7 23 15

    Demo here