Brian, Thanks for the article. I have a question here. I am not gonna post the DDL scripts as I have used your sample tables.

Consider the following statement that you had posted:

Select COUNT(*),ManagerID

From Employees

Group By ManagerID

Order By ManagerID

Now say, I need a third column which shows all the subordinates (IDs in this case) of a manager as csv. Can this be done in a better way than this?

select

e.ManagerID,

COUNT(*) [No of Subs],

(select(stuff((select ',' + cast(EmployeeID as varchar)

from Employees where managerid = e.managerid

for xml path('')),1,1,''))) [SubIDs]

from Employees e

group By e.ManagerID

order By e.ManagerID

I know the requirement (showing IDs as csv) is a little weird but I had to get similar output in some reports. Please comment.

- arjun