Displaying hierarchical data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Scott Schluer (usenet_at_webservicesinc.biz)
Date: 05/15/04 

Date: Sat, 15 May 2004 10:00:39 -0700

Hello All,

I have a table in SQL Server, "Categories" that looks like this:

cat_id int PK
parent_id int (self-referencing, uses a value in cat_id to create
the hierarchy)
cat_name varchar(50)
cat_sort int (just a value to use for sorting)
...

cat_id parent_id cat_name cat_sort
1 0 Category 1 1
2 1 Category 1a 2
3 1 Category 1b 1
4 3 Category 1b1 1

I want to display this information in an ASP.NET datagrid so that it looks
like this
(I intentionally set the sort values for Category 1a and 1b to be reversed
so you can see how it would sort it)

CATEGORY NAME # OF SUB CATEGORIES
category 1 2
----category 1b 1
--------category 1b1 0
----category 1a 0

Now, I know I can do it with a bunch of hits to the database or recursive
sub-routines, etc. However, is there some SQL statement I can use to
generate this in one database hit so that I can just call the stored
procedure and bind the result set to a datagrid? Here are the requirements:

1) Must support an unlimited number of sub-categories.
2) Must be able to count the number of sub-categories that exist immediately
underneath it. It would be nice to know how to drill all the way down to
count ALL of the sub-categories that exist under it, but not necessary for
now.
3) Must add some text maybe 4 " " characters (I used dashes here in
this example) to the start of each category name to provide a visual idea of
the hierarchy. Obviously no spaces are needed at the top level. 4 spaces
are needed at the second level, 8 spaces at the second, 12 at the third,
etc.

That's pretty much it. I know there must be a way to do this, any help is
appreciated.

Thanks,

Scott

Relevant Pages

  • Relational Data & Datagrid
    ... I have a table in SQL Server, ... the hierarchy) ... Must support an unlimited number of sub-categories. ... are needed at the second level, 8 spaces at the second, 12 at the third, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Displaying hierarchical data
    ... Advanced Transact-SQL for SQL Server 2000 ... > the hierarchy) ... > generate this in one database hit so that I can just call the stored ... > 1) Must support an unlimited number of sub-categories. ...
    (microsoft.public.sqlserver.programming)
  • Re: How to handle heirarchies in dimension attributes (Fairly green design question)
    ... Pro SQL Server 2000 Database Design - ... > represent a hierarchy in a dimension table, ... >> Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: How do you think about OODBMS in .NET system?
    ... >I guessed about SQL Server, but it's too complex and needs more codes ... in RDBMS making hierarchy data is most hard work. ... OODBMS is a return to the Network DBMS with some OO features. ... But it is very easy to create your own serialization classes. ...
    (microsoft.public.dotnet.framework)
  • Re: Displaying hierarchical data
    ... > the hierarchy) ... > I want to display this information in an ASP.NET datagrid so that it looks ... > generate this in one database hit so that I can just call the stored ... > 1) Must support an unlimited number of sub-categories. ...
    (microsoft.public.sqlserver.programming)