One of the uses mentioned for common table expressions (CTE) in SQL Server 2005 is recursive queries. A CTE allows you to define a virtual view that can be used in another statement...
This article discusses the creation of user defined aggregates (UDA) in SQL Server 2005.