Hi,

Here is a question where many of you surely have answer:
I've a CATEGORY table like this:
-CATEGORY.CategoryId (auto-incremented integer)
-CATEGORY.CategoryName (char)
-CATEGORY.CategoryParentId (integer)

I need two make a view that render these category into a tree (threaded) view.

what I need in this view:
-ComputedDeep
-CategoryId (auto-incremented integer)
-CategoryName (char)
-CategoryParentId (integer)


CREATE TABLE [dbo].[Category] (
[CategoryId] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [char] (20) COLLATE French_CI_AS NOT NULL ,
[CategoryParentId] [int] NULL
) ON [PRIMARY]

here is a display of what i want to obtain after display process

-Economy
|-Industry
| |-Automobile
| |-Plane
| |-...
| \-...
|-Service
| |-Computer
| \-Internet
-Science

that would give me:

0-1-Economy-<NULL>
1-5-Industry-1
2-18-Automobile-5
2-19-Plane-5
1-7-Service-1
2-20-Computer-7
2-25-Internet-7
0-1-Science-<NULL>

At first look, this kind of process seems very unhandly to code, but maybe someone have a great template or any references on this kind of SQL process

a statement is that I can say there is no loop or circularity in the category table (I suppose this will provide some headache problem...), I'm also in need to sort these categories by Name or count of other elements that references on the CategoryId, is there any advice in order to prepare this?

Hope the question is clear enough and that i'm not asking to much

Thanks alot!

Gauthier