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 using Aggregate Functions 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: