We can use the content by Query web part for rolling up of lists in a single place. But this only supports a single site collection. If you want to show the data from two lists residing in two different site collections, then we can use SharePoint Designer. I just explored with the help of my colleague and it works just fine.
In my example I am using three site collections; root, sites/first and sites/second. I have the default document library; Shared Documents in all site collections.
1. Open the root site collection using SharePoint Designer.
2. Create a new aspx page in the root or in the pages library depending on the requirement.
3. Click on the Task Pane and enable the ‘Data source Library’ and now you can see the same on the right side of the screen.
4. Click on the plus sign on the XML Web Services and click on Connect to a web service.
5. Click on Connect to a web service inside the XML Web Services section.
6. Type the URL for the lists.asmx web service within the first site collection. In our example, provide http://server/sites/first/_vti_bin/lists.asmx and click on the Connect Now button.
7. This will provide the following screen. In operation drop-down, select GetListItems.
8. Double click on the listname from the Parameters section and type the name of the document library / list. In my case, I typed Shared Documents and click ok.
9. Repeat the steps from 5 to 8 for the different list in a different site collection.
10. Now we have two connections to two different lists in two different site collections.
11. The last and final step is to link these two sources through Linked Sources.
12. Click on the plus sign in the Linked sources and click on ‘Create a new linked source’.
13. This will bring a new window and click on ‘Configure Linked Source’
14. In the resulting window, select the two XML web services that we have created and added to the list of Selected data sources and click on Finish.
15. Now click the new linked data source and click on Show Data.
16. The above step will bring the following screen. The last step is to insert the z:row to the page that we have created in step 2.
17. Now it’s your turn to add some beautification to the list.