How to have row values as column headers with the associated values in postgresql?
I've used this good answer of Erwin Brandstetter

But keep in mind that your solution requires a dynamic query because I'm sure that creator table has different records from these you has posted here.




create table creator (id int primary key, name text);
insert into creator values (1, 'jack'),(2, 'doge'),(3, 'cardano');
create table roads(id int primary key, creator_id int references creator(id), length int);
insert into roads values (1, 1, 3), (2, 1, 2), (3, 3, 9), (4, 1, 3);
create table blocks(id int primary key, creator_id int references creator(id), length int);
insert into blocks values (1, 1, 4), (2, 1, 4), (3, 3, 5), (4, 1, 3);



I've used a view to get results for roads and blocks.




create view my_view 
as
select 'roads' as subject, c.name,
       coalesce((select sum(length) from roads where creator_id = c.id), 0)::int as length
from   creator c
union all
select 'blocks' as subject, c.name,
       coalesce((select sum(length) from blocks where creator_id = c.id), 0)::int as length
from   creator c;



✓






select * from 
crosstab 
(
  $$select subject, name, length 
    from my_view
    union all
    select subject, 'total'::text as name, coalesce(sum(length),0)::bigint as length
    from my_view
    group by subject
    order by 1,2$$
)
as t ("subject" text, "cardano" bigint, "doge" bigint, "jack" bigint, "total" bigint)



subject | cardano | doge | jack | total
:------ | ------: | ---: | ---: | ----:
blocks  |       5 |    0 |   11 |    16
roads   |       9 |    0 |    8 |    17



dbfiddle here