How to have row values as column headers with the associated values in postgresql?

Question

This is hard to explain but I'll do my best. So to make it simple, I have 3 tables with the following format:

Creator table

id | name
 1 | jack
 2 | doge
 3 | cardano

roads

id | creator_id | length
 1 |     1      |   3
 2 |     1      |   2
 3 |     3      |   9
 4 |     1      |   3

Blocks

id | creator_id | length
 1 |    1       |   4
 2 |    1       |   4
 3 |    3       |   5
 4 |    1       |   3

I can't seem to pick out the rows into columns, otherwise I would be posting here but the query I use to bring the data together is:

select name, layer, sum(length)
from ( SELect a.name, 'road routes'::text as layer, length
       from creator a
       LEFT JOIN road_routes b on a.id = b.creator
       where a.id in (73, 35, 33, 31)
       group by a.name, layer, length
     union
       SELECT a.name, 'block routes'::text as layer, length
       from creator a
       LEFT JOIN block_route b on a.id = b.route_creator
       where a.id in (73, 35, 33, 31)
       group by a.name, layer, length) x
group by layer, name

The expected result and what I need is a table with the following output:

type   | jack | doge | cardano | total
roads  | 8    | 0    | 9       | 17
blocks | 11   | 0    | 5       | 16

How to sort the data like this?

Answer 1

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