Business Intelligence Geeky/Programming SQLServerPedia Syndication

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



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
	SELECT CONVERT(VARCHAR(4),EmployeeId) + ',' AS [text()]
		FROM HumanResources.Employee e
		WHERE mgr.ManagerId = e.ManagerId

		ORDER BY EmployeeID
	) emp (list)



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 🙂

By Steve Novoselac

Director of Digital Technology @TrekBikes, Father, Musician, Cyclist, Homebrewer

3 replies on “T-SQL: Using CROSS APPLY to Turn 2 Queries Into 1”

Would you know how do the reverse operations? I have the second table (with comma delimited list) and need to query it to get the first table (1 line per value). Any suggestion?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.