Combine Multiple Lists into one

Question

I have a bunch of site collections that all have a identical list( status, Title, Date). I am trying to create a master list on my site that grabs the items from each lists and displays them all together with one extra column called ( Owner ) which will say where the item came from (which site it was on). Can anyone point me in the right direction on how to accomplish this? Is this possible with built in features or do i need to build something custom? Anything to get me started woulf be great. I am using share point 2010. thanks.

Edit - the combined list will not need full functionality, all I plan to do id have the title be a link to the item on its own site collection.

Edit -

There will be something like 5-10 site collections with one list from each site collection. the list is a task list and we want to combine them all and allow sorting and grouping to get a better handle on all of the tasks needed to be done. this will be accessed multiple times a day so i think a custom web part is a good way to handle it. I have started developing a custom visual web part but I am having a hard time accomplishing what i want. Right now i have a standard table where I pull the lists values into, this is good for displaying all task items but it does not allow me to sort or group the items afterwards (unless I am missing something).

Does this further info help?

Answer 1

Because you are going across site collection boundaries, your options are:

• Search - Provided you have these lists created with a the same content type for the list items, you could create a search scope to return the items of this content type. You might need to adjust the search results XSL a bit to meet your needs. You might reference this link: http://sharepoint.licomputersource.com/2010/11/configure-custom-search-scope-in-sharepoint-2010-to-rollup-tasks-documents-or-sites-from-anywhere-part-1/ (found with a quick Bing search). Also, this link should help get going with using the Search Results web part and customizing the query/display: http://msdn.microsoft.com/en-us/library/ee819886. If the requirement is to see all items, regardless of item permissions, this won't work.
• Custom Developed - If the requirement is to see all items, regardless of permissions to actually get at them, you might look to a custom solution. You might store references to all items in a master list somewhere. This could be done from a Timer Job (probably my first choice), Workflow, or Event Receiver, depending on what the security/access permissions were. Depending on how many site collections you are talking about, and assuming you could work in a Farm Solution (not Sandboxed), you might be able to leverage the Server Side Object Model in a web part, but this could be asking for trouble to do that aggregation each time (if you did pursue this, I'd look at caching the data).
• Purchased - the big vendors may have things that aggregate across lists. Bamboo comes to mind. As mentioned in comments, Lightning Tools (http://www.lightningtools.com/lightning-conductor-2010-web-part.aspx). Cannot speak to either as I've not used them personally.
• PowerShell - if this is only needed by a small subset of users (like 1-2), you might also consider a PowerShell script that is run as a scheduled task which dumps the results to a .csv (comma separate file), then import this data into a list. You could import with PowerShell, or manually using DataSheet View. Once loaded into a list, you could then use the standard list web parts for display.

Hope this helps.