Out of the box SharePoint ships with the User Tasks Web Part which allows you to display tasks assigned to the current user in the current Site.
If you want to display all Tasks in SiteCollection you can use CQWP or DataView. But there is an easy way to do that for the same user experience. The best approach is to change the value of a hidden property of the User Tasks Web Part QuerySiteCollection. This property is hidden and cannot be modified through the UI.
First export User Tasks WebPart and add property:
before the </WebPart> tag
And import again. Check the result.
This will work with SharePoint 2007/SharePoint 2010.