sql server - t-sql : get a single row with parents, children, grandchildren... n children -

I have a table with 2 columns: id, parent id

I want to create a new table / see that each parent includes a certain guard at all levels of the parents ...

For example:

  ParentID Level_1 Level_2 Level_3 .. level_n   
 This means that parents are parents of level_1 who are the parent of Level 2, parents of the level and so on ...  
 i do it without reinterpretation There is no need to stack overflow, so ...  
 In T-SQL, there is a function called " So choose, I can use it somewhere in the loop or something ...  
 The question is how many columns can I get to create in the beginning? How to use this challenge?   
 
 
 The one you are trying to achieve is a pivot table, but, in my opinion, This work is not related to the database. I should instead get a result set with rows and pivot them "externally".  
 To get everything you have described in SQL Server 2005+, you get a generalized table expression (whereas SQL Server 2000 requires a slightly different approach.  
  Note: : Although you have not written "no recurrence, so there is no chance to stack overflow", you still have to find yourself with unlimited bounds, and setting a maximum "depth" for it, When attempting to go "deep" from the range of recursions So, so I'm going to stop it and return an error.