Complex Cross Apply
By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP
February 10, 2007

The CROSS APPLY operator combines a table or query with a table-valued function operating on the cells of the table or query. CROSS APPLY can be used to construct complex reports.

CROSS APPLY returns only rows from the outer table or query that produce a result set from the table-valued function with cells in the row as input parameters. The CROSS APPLY operator allows you to invoke a table-valued function for each row returned by an outer table or query expression. The table-valued function acts as the right input and the outer table or query expression acts as the left input in an equi-join. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the CROSS APPLY operator is a SELECT subset of the set of columns in the left input followed by the list of columns returned by the right input.

In the following example we calculate financial statistics for each dealer which sells AdventureWorks mountain bikes and associated products.

Here is the listing:

USE tempdb
GO
-- drop FUNCTION dbo.fnOrderFingerprint
CREATE FUNCTION dbo.fnOrderFingerprint(@CustomerID AS int)
RETURNS TABLE
AS
RETURN
SELECT Label='Maximum $', TotalDue=max(TotalDue)
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
UNION
SELECT Label='Average $', TotalDue=avg(TotalDue)
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
UNION
SELECT Label='Minimum $', TotalDue=min(TotalDue)
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
UNION
SELECT Label='Order Count', TotalDue=count(TotalDue)
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
UNION
SELECT Label='Standard Deviation $', TotalDue=stdev(TotalDue)
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID

GO

SELECT Customer=S.Name,
F.Label,
[Total Due] = left(convert(varchar,convert(money,F.TotalDue),1), len(convert(varchar,convert(money,F.TotalDue),1))-3)
FROM AdventureWorks.Sales.Store AS S
JOIN AdventureWorks.Sales.Customer AS C
ON S.CustomerID = C.CustomerID
CROSS APPLY
tempdb.dbo.fnOrderFingerprint(C.CustomerID) AS F
ORDER BY
Customer ASC, Label DESC

GO

This is the partial report: