sql server - sql to return nested result set? -

is possible have single sql query return nested structure rather doing recursive db calls build array or object?

i'm using similar pseudo code build :

parentcategory = 'select *                      category                     child_category null                       , parentidno null';     while parentcategory       childcategory = 'select *                           category                          parent_id = parentcategory.id';     if (parentcategory.id)       recursive  'select *                         category                        parent_id = parentcategory.id'; end         cat_1     -child_1     -child_2     --grandchild_1       cat_2     -child_1     -child_2     --grandchild_1 

check out recursive ctes assumes sql 2005 or later