Postgres CrossTab for query with variable number of columns

Question

Context

I am running a variant of the vehicle routing problem for a site. The layout is this. You have pick up points, trucks and items being picked up. The real trick is that a customers don't run the same number of shifts in a day, so that is customizable. So I will know this at runtime but not in advance. I am trying to create query that returns a result with rows for each bus stop, columns for each shift and json arrays of the ids of each item, like this:

id  |     address      |   city    | postalcode |  shift1  | shift2 | shift3
-----+------------------+-----------+------------+----------+--------+--------
 220 | 1471 N Lymestone | Anytown  | 12345      | [14, 16] | [12]   | [14]
 221 | 108 D Ave        | Anytown  | 12345      | [15, 17] |        | [15,16]
 222 | 1434 Bryan Ave   | Anytown  | 12345      | [16]     | [1,19] |

Table Structure

I have three relevant tables here; the stops table that just has geographic locations of stops, the items table that has ids of pickup items, the shifts table that has the shifts and the itemstopassignemnt table (please forgive the naming, I inherited it) that has an stop_id shift_id and item_id assigning an item to be picked up at that stop for that shift.

Query

So after fishing around on SO, I came up the following query:

SELECT * FROM crosstab(
     $$SELECT stop_id, 
              'shift' || shift_id, 
              json_agg(item_id) 
       FROM itemstopassignemnt 
       GROUP BY stop_id, shift_id 
       ORDER BY 1,2$$
) AS (stop_id INTEGER, shift1 JSON, shift2 JSON, shift3 JSON);

This works for the three shift case. And I can put this in function generate the sql programmatically so it constructs it dynamically for however many shifts the user has added. The function then would have to return setof record or table(), I think. Here are my questions:

Is this the best approach to the problem?
Can this be done with crosstabs alone and not with dynamically generated sql?
If not, then whats the best way to set up the function? Specifically what return type should I use and, since I could have done this programmatically without crosstabs anyway (loop through shifts table and create subselects) is there an advantage to using crosstab here?

There are several approaches, but none of them is such that the client does `select * from magic_pivot(some parameters)` and get back the expected result. See for instance http://stackoverflow.com/questions/39779734/dynamically-generate-columns-in-postgresql and what it links to. — Daniel Vérité, Apr 25 '17 at 11:03
The only way to have it without dynamic sql is to return json. How will the result be consumed? If it is a web page then json is perfect. — Clodoaldo Neto, Apr 25 '17 at 11:30
Json is an option but this particular query was going to be joined to the stops table via a subselect. How would that work? Im out now but will update the question with the details of the full query soon. — Frank Conry, Apr 25 '17 at 11:36
@ClodoaldoNeto I changed the desired result query but I guess I'm still not seeing how to use json to accomplish this even if it were only the subquery. It is used by a web app so if json were possible it would work nicely. — Frank Conry, Apr 25 '17 at 15:51

Clodoaldo Neto · Accepted Answer · 2017-04-26T08:19:44.163

http://rextester.com/RPV59686

select
    stop_id,
    jsonb_object_agg('shift' || shift_id, items) as shift_items
from (
    select
        stop_id,
        shift_id, 
        coalesce (
            array_agg (item_id) filter (where item_id is not null),
            array[]::int[]
        ) as items
    from
        itemstopassignemnt
        right join
        (
            select shift_id, stop_id
            from
                (select distinct shift_id from itemStopAssignemnt) a
                cross join
                (select distinct stop_id from itemStopAssignemnt) b
        ) r using (stop_id, shift_id)
    group by 1,2 
) s
group by 1
;
 stop_id |                      shift_items                       
---------+--------------------------------------------------------
     220 | {"shift1": [14, 16], "shift2": [12], "shift3": [14]}
     221 | {"shift1": [15, 17], "shift2": [], "shift3": [15, 16]}
     222 | {"shift1": [16], "shift2": [1, 9], "shift3": []}

I did not join it to stops to shorten it, but should be trivial.

This is an awesome solution! I modified it slightly, in particular the central cross join is between the shift table and the stops table. And yes the full join with the stops table is trivial. — Frank Conry, Apr 26 '17 at 09:35

Postgres CrossTab for query with variable number of columns

1 Answers1