I have tried to keep the dynamic SQL executions to a minimum for obvious speed reasons. What I think I have provided is a base for creating crosstabs/pivots for users to learn from and hopefully, evolve with.
There are some things to do with the code to make it "universal". But I thought it would be best to show how to start from the beginning.
To make the code "universal" you first have to build a dynamic query for preaggregating the data in #Aggregates. That is not very hard to to! Also, you must change the parameters to the future stored procedure to allow two fully qualified names such as RemoteServer1.OwnerLocal.ThatTable.ThisField for the rows and columns. Even the CellData field in #Aggregates could be taken from a parameter this way and called with 'SUM(x)' or 'COUNT(y)'. You catch my drift.
An example could be
EXEC dbo.CrossTabPivot 'Table1.OfficeName', 'Table2.Category', 'COUNT(t)', ...