Relational Data & Datagrid

[Scott Schluer]

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 a 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

 

[Alfredo]

I want to display this information in a 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

Why not a treeview?

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?

select * from categories order by cat_id, cat_sort

And build the tree recursively in code.


Regards
Alfredo

 

[Scott Schluer]

Because that's not what I want to do.

Why not a treeview?


select * from categories order by cat_id, cat_sort

And build the tree recursively in code.


Regards
Alfredo

 

[Alfredo]

Because that's not what I want to do.

Then use a datagrid, but it is the same


Regards
Alfredo