CODEWITHSUNDEEP
sqlsql-servert-sqlhierarchical-data
Ronn
Ronn

Reputation: 1773

sql to return nested result set?

Is it possible to have a single sql query return a nested structure rather than doing recursive db calls to build up the array or object?

I'm using something similar to this pseudo code to build up :

parentCategory = 'SELECT * 
                    FROM Category 
                   WHERE child_category IS NULL 
                     AND ParentIDNo IS NULL';   

while parentCategory do
      childCategory = 'SELECT * 
                         FROM Category 
                        WHERE parent_id = parentCategory.id'; 
   if (parentCategory.id)
      do recursive  'SELECT * 
                       FROM Category 
                      WHERE parent_id = parentCategory.id';
end  


    Cat_1
    -child_1
    -child_2
    --grandchild_1  
    Cat_2
    -child_1
    -child_2
    --grandchild_1

Upvotes: 2

Views: 5458

Answers (3)

Michael Buen
Michael Buen

Reputation: 39393

That's very possible if you are using SQL 2005 and up. For a primer on hierarchical query see this: http://www.ienablemuch.com/2010/04/simple-hierarchical-query-display.html

Upvotes: 0

Avitus
Avitus

Reputation: 15958

If you modify your table to contain a delimited path to the top of the tree then you don't have to do this recursively.

For example if you had the tree path be "cat_1|child_2|grandchild_1" for the grandchild_1 node of the tree then you would be able to split the string for loading into a tree as well as be able to determine the level in the tree you were at. Also when you select from the table you would be able to order by the path and the tree would come out exactly as you wanted to draw it.

the negative is that you would have to maintain this path on any changes to the tree.

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

Check out recursive ctes assumes sql 2005 or later

Upvotes: 5

Related Questions