You need a function to accomplish this. I wrote a simple script for you.
CREATE FUNCTION [dbo].[GetChildName](@parentId INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @ChildName AS VARCHAR(10)
DECLARE @Names AS VARCHAR(8000)
SET @Names = ''
DECLARE TempCur CURSOR FOR
SELECT [Name] FROM Table2 WHERE ParentId = @parentId
OPEN TempCur
FETCH TempCur INTO @ChildName
WHILE @@fetch_status = 0
BEGIN
SET @Names = @Names + ' ' + @ChildName
FETCH TempCur INTO @ChildName
END
CLOSE TempCur
DEALLOCATE TempCur
RETURN @Names
END
after you create the function, use this as your select.
SELECT ID, ParentsName, dbo.GetChildName(ID) AS 'ChildName'
FROM Table1
Hope this helps.