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?

• Krismorte: You need to create tablefunc postgresql.org/docs/9.1/static/tablefunc.html Follow this tutorial vertabelo.com/blog/technical-articles/…
• McNets: crosstab (pivot) solutions requires a well known number of columns, otherwise you should build it dynamically.

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

• Luffydude: The fiddle is exactly what I need, thanks! You're right, there are a lot more creators but for this task I only need 4 creators listed plus the total sum
• McNets: I'm glad to be able to help.