Getting recursive values of sub records in MSSQL 2005

I have a table called "categories" which contains all categories of "widgets".  Categories can be "sub_of" as well.  There is a field.  The structure looks like.

category_id int id
category_name varchar (50)
sub_of int

The data look like this

category_id             category_name                        sub_of
1                             Widgets                                        0   -  this is the root.
2                             Nice Widgets                                 1
3                             Cool Widgets                                1
4                             Very Cool widgets                         3

In my "widgets" table there is reference (fk) to category_id to categorize the widgets.

Question - I need to create a query to quickly return the contents and counts in each category.

For example, if I had 10 widgets total, the counts would look like:

>Widgets (10)
    >Nice Widgets (5)
    >Cool Widgets (5)   (note: 3 in this category and two below).
        > Very Cool Widgets (2)

So if I ask for all Widgets in "widgets" I get all widgets not only spefically in that category, but in all sub categories as well.  I also need the counts in similar manner.

I need actual code examples please.

Thank you.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
ok, lets' see to get part 1 done, the recursion without the counts yet
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the hierarchical part is easy, using the CTE method in SQL 2005...
did you achieve that already?
ccleebeltAuthor Commented:
no - I am just getting this started.
ccleebeltAuthor Commented:
actually, this does it...thanks.  I can just add the count(*) to the last query and go from there.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.