Run Code
|
Code Wall
|
Users
|
Misc
|
Feedback
|
About
|
Login
|
Theme
|
Privacy
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.
Game Play Analysis (511,512,534,550)
Mandelbrot
testo
arjun
Projeto
Return IDs where there are exactly 3 occurrences
Shortest "path" between two employees across companies
PC02-DB-UTEC
Descriptor test
filme
Please log in to post a comment.