SELECT
	a.ChildName,
	b.ChildName,
	c.ChildName
FROM
	YourTable a
	INNER JOIN YourTable b ON a.ChildId = b.ParentId
	INNER JOIN YourTable c ON b.ChildId = c.ParentId
WHERE
	a.ParentId IS NULL;

quote:

---

Originally posted by phrankbooth

Thanks for you r prompt response, I actually have a large/unknown number of levels. What would this query look like then?

I know about CTE, the problem I'm having is I don't know how to extend out the table columns for each new level in the hierarchy.

--PhB

---

-- My simplified test table----------------------------------------------
CREATE TABLE #tmp (cid INT , cname VARCHAR(32), pid INT, pname VARCHAR(32))

INSERT INTO #tmp VALUES
(1,'p1',NULL,NULL),(2,'p2',NULL,NULL),(3,'c1',1,'p1'),(4,'c2',2,'p2'),(5,'g1',3,'c1'),(6,'g2',4,'c2');

-- Recursive query-------------------------------------------------------
;WITH cte AS
(
	SELECT 
		*, 
		ROW_NUMBER() OVER (ORDER BY cid) AS RN,
		1 AS lvl
	FROM #tmp a 
	WHERE 
		NOT EXISTS (SELECT * FROM #tmp b WHERE a.cid = b.pid)
		
	UNION ALL
	
	SELECT 
		t.*,
		c.RN,
		c.lvl+1
	FROM 
		#tmp t 
		INNER JOIN cte c ON t.cid = c.pid
),
cte2 AS
(
	SELECT cname, lvl, RN FROM cte
)
--- Static pivoting------
SELECT 
	*
FROM
	cte2
PIVOT
( MAX(cname) FOR lvl IN ([1],[2],[3])) P;

-- CLEANUP -------------------------------------------------------
DROP TABLE #tmp;