It would be great to have an OOTB geoprocessing tool that could create a query layer that is a pivot table/query. The processing would be done dynamically by the database, not the application, just like other query layers. Similar to the Make Aggregation Query Layer tool.
Related:
