How to output the results of an procedure in SQL server?

Creating hierarchy results table in SQL Server 2005?

• I'm trying to create a list of galleries in parent - child order so the results would come out like Parent Gallery 1 Parent Gallery 1s children Parent Gallery 1s children children Parent Gallery 2 Parent Gallery 2s children Parent Gallery 2s children children and so on Each gallery has an id and parentid, the top level gallery have parentid of 0. What I would like to do is return the id and gallery name for each gallery in the order above. Is it possible to do this using tsql, CLR etc.

• Answer:

  Try the WITH common table expression, perhaps like this: WITH t (name, id, level, path) as (SELECT name, id, 0, cast('\' + name as varchar(MAX)) path FROM galleries WHERE parentid = 0 UNION ALL SELECT g.name, g.id, t.level + 1, t.path + '\' + g.name FROM galleries g inner join t on t.id = g.parentid) select replicate(' ',level) + name Name, path from t order by path The key to this is using a self-join within the WITH common table expression to get the recursion.