Execute the following
T-SQL script in Management Studio Query Editor to demonstrate the use of CROSS APPLY.
The following CROSS APPLY main query represent a special "JOIN " between an outer and a correlated inner query with the GROUP BY clause. Only matching results are returned:
USE AdventureWorks;
-- The outer query adds customer name to the inner query aggregation results
-- SQL cross apply - SQL group by - SQL correlated subquery
SELECT
[Customer] = s.Name,
-- This is a special money data type currency formatting option
[Total$ Sales] = '$' + Convert(VARCHAR,Convert(MONEY,SalesAmount.OrderTotal),1)
FROM Sales.Customer AS c
-- The customer name is in this table
INNER JOIN Sales.Store AS s
ON s.CustomerID = c.CustomerID
-- The inner query is a correlated GROUP BY subquery
CROSS APPLY (SELECT soh.CustomerId,
Sum(sod.LineTotal) AS OrderTotal
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderId = soh.SalesOrderId
-- This is the correlation to the outer query
WHERE soh.CustomerId = c.CustomerId
-- Filter data
AND Year(OrderDate) = 2004
AND Month(OrderDate) = 1
GROUP BY soh.CustomerId) AS SalesAmount
ORDER BY [Customer]
GO
/* Partial results
Customer Total$ Sales
Activity Center $9,630.73
All Cycle Shop $167.98
All Seasons Sports Supply $2,159.35
Amalgamated Parts Shop $29,625.69
Area Bike Accessories $57,449.60
Basic Bike Company $65.99
Best Cycle Store $37,202.78
Best o' Bikes $4,443.73
Better Bike Shop $29,140.80
Bicycle Exporters $8,043.03
*/
Related article:
All the Fuss about CROSS APPLY
|
