SharePoint offers some powerful Web Parts that allow you to share content across sites.

The Content Editor Web Part is a universal plug adapter. Put it on a SharePoint page, and suddenly your page gets connected to the rest of the world: you can add html/CSS/JavaScript/ jQuery, embed videos or widgets, display content from other SharePoint sites, etc. To see it in action, explore my blog, Iain’s site or endusersharepoint.com.

The Data View Web Part, SharePoint’ s Swiss Army knife, lets you customize SharePoint lists, aggregate them or display them in another site. It can also connect to other data, like RSS feeds or external applications. One constraint: you need SharePoint Designer to customize it. Microsoft has made it more accessible recently, as since last April SPD is free.

MOSS offers a third powerful tool: the Content Query Web Part.

From Microsoft:
Content Query is a Web Part that displays a dynamic set of items based on a query that you build by using a Web browser. You use the query to specify which items are displayed, and you can set presentation options to determine how those items are displayed on the finished page.

I must say that I was disapointed by Microsoft’ s documentation on the CQWP. For example they don’t state up front that the scope of a CQWP is restricted to a site collection. Fortunately the Web provides many helpful references.

If you’re new to the CQWP, you can start with this screencast:
http://www.click2learn.ch/Documents/ContentQuerWebpart.html 
(Steps 1 to 132 set the scene, and the CQWP makes an entrance at step 133)

Out of the box, you’ll find that the CQWP has limited customization options. It will only return a handful of fields and display them as a simple list. However, you can go beyond this, and the great news for end users is that all this customization can be done on the client side. Be prepared for a tough ride though, customizing the CQWP is not for the faint of heart. To get what you want, you’ll need a minimum knowledge of XSL, or copy/paste skills.

I am not going to repeat what has already be written by others, so I have collected below a list of the best references I have found. I’ll just highlight a few points:
– the CQWP only comes with MOSS, it is not available in wss.
– activate the publishing feature to make the CQWP available in your site collection (Site Actions | Site Settings | Site Collection features)
– you can only query content that resides within the same site collection.
– after activating the publishing feature, end users have access to the stylesheets. They are stored in a dedicated library called the “Style Library”.
– as an end user, you can customize the CQWP by using the tool pane, or you can export it to your computer and edit it (using Notepad, etc.).

 Note that I haven’t included in my list posts that specifically target developers.

Must read

Configuring and Customizing the Content Query Web Part Microsoft Enterprise Content Management (ECM) Team Blog
Customizing the Content Query Web Part and Custom Item Styles Heather Solomon
Display Content Query Web Part Results in a Grid / Table Paul Galvin

Tips and tricks

Content Query Web Part xsl for showing the xml Ishai Sagi

Showing the description of a page in the Content Query Web Part as rich HTML Ishai Sagi

Teach the Content Query Web Part how to display a link list Ishai Sagi

Quick Tip: Content Query Web Part, Lookup Column Value and XSL Paul Galvin

How to customize the Content Query Web Part XSL to aggregate blog posts Henry Ong

Content Query Web Part (CQWP) with Anonymous Access Mike Geyer

 Other references

How to: Customize XSL for the Content Query Web Part MSDN

Customizing the Content Query Web Part XSL Steven Van de Craen

Display data from multiple lists with the Content Query Web Part Microsoft (from within SharePoint Designer)

Add dynamic content to a page Microsoft’s help page on content query

Waldek Mastykarz

Baris Wanschers

Andy Burns

Filter Content Query Web Part by file type Itay Shakury

List of field types

Kartic’s blog

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfieldtype.aspx MSDN

Content Query Web Part – Field Types Brian Caauwe

Limitations

Content Query limitations

Content Query Web Part (CQWP) SharePoint 2007 Performance Ranjan Banerji

Others

The Enhanced Content Query Web Part Codeplex project by Ishai Sagi