/*
Level0 - Head(NoParent)
Level1 - Child of Level0(Parent)
Level2 - Child of Level1(Parent)
Level3 - Child of Level2(Parent)

Id Name ParentId
--------------------
1 A0 Null
2 A1 1
3 A2 2
4 B2 2 
5 A3 3
6 B3 3
7 C3 3
*/

DECLARE @startId INT
SET @startID = NULL

DECLARE @data TABLE (
	[ID] INT PRIMARY KEY
	, [Name] VARCHAR(255)
	, [PARENTID] INT
	)

INSERT @data ([ID], [Name], [PARENTID])
      SELECT 1, 'A0', NULL
UNION SELECT 2, 'A1', 1
UNION SELECT 3, 'A2', 2
UNION SELECT 4, 'B2', 2 
UNION SELECT 5, 'A3', 3
UNION SELECT 6, 'B3', 3
UNION SELECT 7, 'C3', 3

-- Show Base Data
SELECT * FROM @data

-- CTE (This works on sql server 2005 and greater)
; WITH tree (
	[startID]
	, [nodeID]
	, [startName]
	, [endName]
	, [nodePath]
	)
AS (
	SELECT
		[ID]
		, [ID]
		, [name]
		, CAST('' AS VARCHAR(255))
		, CAST([name] AS VARCHAR(MAX))
	FROM
		@data
	WHERE
		[ID] = @startId OR @startId IS NULL

	UNION ALL SELECT
		p.[startId]
		, l.[Id]
		, p.[startName]
		, l.[name]
		, p.[nodePath] + ' -> ' + CAST(l.[name] AS VARCHAR(MAX))
	FROM
		tree p
		JOIN @data l ON l.[parentID] = p.[nodeID]
	)
SELECT
	t.[startID]
	, t.[startName]
	, LEFT(t.[children], LEN(t.[children]) - 1) AS [children]
FROM
	(
	SELECT
		[startId] AS [startId]
		, [startName] AS [startName]
		, (SELECT l.[endName] + ', ' FROM tree l WHERE l.[endName] <> '' AND l.[startId] = p.[startID] FOR XML PATH ('')) AS [Children]
	FROM
		tree p
	WHERE
		[endName] = ''
	)
	t