You can dynamically create a view. The idea and the solution for a simpler case than yours is described in this answer. Please read it before continuing.
We will use the following query to create a view:
with all_locations(location) as (
select distinct location_a
from locations
union
select distinct location_b
from locations
)
select location_a as location, json_object_agg(location_b, count order by location_b) as data
from (
select a.location as location_a, b.location as location_b, count(l.*)
from all_locations a
cross join all_locations b
left join locations l on location_a = a.location and location_b = b.location
group by 1, 2
) s
group by 1
order by 1;
Results:
location | data
----------+----------------------------------------------------------------------------
Atlanta | { "Atlanta" : 0, "London" : 1, "New York" : 0, "Sydney" : 0, "Tokyo" : 0 }
London | { "Atlanta" : 0, "London" : 0, "New York" : 0, "Sydney" : 0, "Tokyo" : 0 }
New York | { "Atlanta" : 0, "London" : 0, "New York" : 0, "Sydney" : 0, "Tokyo" : 1 }
Sydney | { "Atlanta" : 0, "London" : 1, "New York" : 0, "Sydney" : 0, "Tokyo" : 0 }
Tokyo | { "Atlanta" : 0, "London" : 0, "New York" : 0, "Sydney" : 2, "Tokyo" : 0 }
(5 rows)
The list of cities will be used twice inside the function, so it is stored in the array cities. Note, that you can replace the first query in the function to a simpler one (it is simply ordered list of distinct cities).
create or replace function create_locations_view()
returns void language plpgsql as $$
declare
cities text[];
list text;
begin
-- fill array with all cities in alphabetical order
select array_agg(location_a order by location_a)
from (
select distinct location_a
from locations
union
select distinct location_b
from locations
) s
into cities;
-- construct list of columns to use in select list
select string_agg(format($s$data->>'%1$s' "%1$s"$s$, city), ', ')
from unnest(cities) city
into list;
-- create view from select based on the above list
execute format($ex$
drop view if exists locations_view;
create view locations_view as
select location, %1$s
from (
select location_a as location, json_object_agg(location_b, count order by location_b) as data
from (
select a.location as location_a, b.location as location_b, count(l.*)
from unnest(%2$L::text[]) a(location)
cross join unnest(%2$L::text[]) b(location)
left join locations l on location_a = a.location and location_b = b.location
group by 1, 2
) s
group by 1
) s
order by 1
$ex$, list, cities);
end $$;
Use the function and select data from the created view:
select create_locations_view();
select * from locations_view;
location | Atlanta | London | New York | Sydney | Tokyo
----------+---------+--------+----------+--------+-------
Atlanta | 0 | 1 | 0 | 0 | 0
London | 0 | 0 | 0 | 0 | 0
New York | 0 | 0 | 0 | 0 | 1
Sydney | 0 | 1 | 0 | 0 | 0
Tokyo | 0 | 0 | 0 | 2 | 0
(5 rows)
I used this method several times but I have no experience with really large data, so I cannot guarantee that it is efficient.
