SQL - The Power of APPLY

MS SQL 2005 and up adds support for the APPLY clause, which, in turn, lets you join a table to any dynamic sets, such as table-valued-functions or even derived tables. While we can argue over the benefits and dangers of the latter (another set of article perhaps?), being able to do things more than one way is certainly always awesome.
The two new functions (CROSS APPLY and OUTER APPLY) are essentially INNER and OUTER JOINs for table-functions (you cannot directly join them like tables). Here is just a simple example of the usage (don’t hesitate to expand on it):

[syntax_prettify linenums=”linenums”]SELECT All.CustomerID
FROM
Customer as C
CROSS APPLY
fnSelectAllChildren(C.CustomerID) AS All
WHERE C.Status = 1[/syntax_prettify]

This technique can also be used in more complex queries alongside derived tables, FOR XML, etc., covering the need for things such as inline multi-row CONCAT (multiple rows into a single column):

[syntax_prettify linenums=”linenums”]SELECT
ito.ID,
(
SELECT TOP 1
ISNULL(LEFT(o.list, LEN(o.list) - 1), ‘Unknown’)
FROM
Orders AS ito2
CROSS APPLY (
SELECT
CONVERT(VARCHAR(12), ServiceType) + ‘,’ AS [text language=”()”][/text]
FROM
OrderService AS itos2 (NOLOCK)
JOIN
OrderServiceType AS itost (NOLOCK)
ON
itost.Id = itos2.OrderServiceType
WHERE
itos2.OrderId = ito.ID
FOR
XML PATH(‘’)
) o (list)
WHERE
ito2.ID = ito.ID
) AS AllTypes
FROM
Orders (NOLOCK) ito
WHERE
ito.Status = 0[/syntax_prettify]