Postgres CrossTab for query with variable number of columns, PostgreSQL

Postgres CrossTab for query with variable number of columns

create table itemStopAssignemnt (
    stop_id int, shift_id int, item_id int
);
insert into itemStopAssignemnt (stop_id, shift_id, item_id) values
( 220,1,14),
( 220,1,16),
( 220,2,12),
( 220,3,14),
( 221,1,15 ),
( 221,1,17 ),
( 221,3,15 ),
( 221,3,16 ),
( 222,1,16),
( 222,2,1),
( 222,2,9);
create table stops (
    stop_id int, address text, city text, postalcode int
);
insert into stops (stop_id, address, city, postalcode) values
 
(220,'1471 N Lymestone','Anytown',12345),
(221,'108 D Ave','Anytown',12345),
(222,'1434 Bryan Ave','Anytown',12345);

select
    stop_id,
    jsonb_object_agg('shift' || shift_id, items) as shift_items
from (
    select
        stop_id,
        shift_id, 
        array_agg (item_id) 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
;


run \| edit \| history \| help	0

Please log in to post a comment.