T-SQL: Using CROSS APPLY to Turn 2 Queries Into 1

Parent/Child. Order/Line. Header/Detail. Report/Sub-Report. We have all ran across these database designs and have had to write queries off of them. You usually end up having the parent id, and you need to get the children. Or you have a list of parents and need to loop through them, looking up the children records and doing something with them. But what if you just want a list of the children id’s (or names, or whatever). Do you really need to loop through the parents and lookup all the children, (and possibly look through those)? You can do JOIN’s and you can get the data in a tablular format, but how do you rollup those children records?

Using the AdventureWorks DB in SQL 2005, an example using Manager/Employee:

SELECT DISTINCT mgr.ManagerId, e.EmployeeId
	FROM HumanResources.Employee mgr
	INNER JOIN HumanResources.Employee e ON mgr.ManagerId = e.ManagerId

Results:

But really we want to rollup those employees, ending up with one manager/employee record, ex: 3, [4,9,11,158,263,267,270] … for this, try CROSS APPLY

SELECT DISTINCT ManagerId, Employees = LEFT(emp.list, LEN(emp.list)-1)
	FROM HumanResources.Employee mgr
	CROSS APPLY
	(
	SELECT CONVERT(VARCHAR(4),EmployeeId) + ',' AS [text()]
		FROM HumanResources.Employee e
		WHERE mgr.ManagerId = e.ManagerId

		ORDER BY EmployeeID
		FOR XML PATH('')
	) emp (list)
WHERE mgr.ManagerId IS NOT NULL

Results:

As you can see from the results, we rolled up our employees into one record per manager, into a comma delimited list. Think of some possibilities of using CROSS APPLY in your apps or stored procs/reports to reduce the number of queries you might have to write, or number of trips to the database you might have to do. Happy T-SQL’ing 🙂