Getting Rows of data as a single column in a SQL query

Recently, I've been working on a Sharepoint Based document workflow solution that uses a custom SQL database. I ran into an issue where I needed to join a database table and get many rows of data back as one column in a query.

For example:
Table A:
ID, Name,

Table B:
childName, ParentID

where parent ID is table A.ID


I needed my results to be in the format:
A.ID, name, (childName1, ChildName2,ChildName3,....)


I found a few options,
One: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Provided about a dozen different ways to do it. All of them looked overly complex.

Then i stumbled upon:
http://johnnycoder.com/blog/2006/09/05/concatenate-multiple-rows-into-a-single-string/

which provided a simple Scalar function to return the results. I took that method and put together a SQL Scalar function to do it:

CREATE FUNCTION GetChildrenFromParent
(


@ParentID varchar(15)
)
RETURNS varchar(5000)
AS
BEGIN

DECLARE @ChildList varchar(5000)

SET @ChildList = ''

select @ChildList = coalesce(@ChildList + ', ', '') + childName from B where ParentID=@ParentID



-- Return the result of the function
RETURN @ChildList
END
GO


Then my SQL to get the data looks something like:
select *,GetChildrenFromParent(id) as ChildNames from A

This returns results like:

1|Jeff| andy,mark,shannon

so, the one column is a comma separated list of rows of data from another table.