With SharePoint web services and dataview web part, You can aggregate the lists/document libraries/etc between sub-sites/site collections/web applications/even across forms.
So, here I’m going to walk through how to achieve this using a simple example. Let’s say I’ve two sites namely: Site1 and Site2 and I have an Announcement List 1 in Site 1 and Announcement List 2 in Site2.
Now, What I want to create is: in my portal, I would like to create a consolidated view that has both the announcements from site 1 and site2. Follow the below steps.
1. Open the SharePoint designer and Create a Aspx page
2. Go to Dataview >> Click on “Manage data sources”
3. Expand the “XML Web Services” node in Data source Library” and click on Connect to a web service
4. Enter the web service URL: https://sitename/_vti_bin/lists.asmx , click on “Connect Now” then, select the GetListItems Method. Enter the List Name. Supply the Login parameters.
Specify the web service URL and pick: GetListItems operation.
5. Do the same for all other lists you would like to connect. Finally you will have the data sources like this:
6. Now expand the “Linked sources” in data source Library and add the web services sources you have created.
7. Insert a dataview into the Aspx page you created.
8. Drag and Drop the z:row From Linked source to Dataview webpart.
9. Finally, you will get the merged data.
10. Do the cleanup, change the column headers, apply the master page, Filter!
Here is my another post on comparing third party tools for the same purpose: List Aggregation/Rollup In SharePoint -Third Party Products Comparison