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 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
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 :)