SQL Query for category / subcategory / subcategories display

I'm stumped by something that I was almost certain that I've done fairly easily in the past...

I have a simple table of categories like this:

ID int (identity)
SubCategoryOf int (nullable)
Name

Where each row is a category, and possibly a subcategory of another category.  What I want to do is display this data something like this.

Category 1
--- SC1
------ SC2
--- SC3
Category 2
--- SC4
--- SC5
------ SC6
---------SC6

Any suggestions for the select code which could accomplish this?  I could limit the number of levels available, but this isn't ideal.  If it helps, this will be for use in a drop down list with ASP.Net (I'll be using the ID of each category when selected).

Thanks in advance.
LVL 2
PsychotextAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
check this example
 

SELECT *
INTO #tmpTable
FROM (
SELECT 1 AS [ID], 'A' AS Label, NULL AS ParentID
UNION
SELECT 2, 'Aa', 1
UNION
SELECT 3, 'Aa1', 2
UNION
SELECT 4, 'Aa2', 2
UNION
SELECT 5, 'B', NULL
UNION
SELECT 6, 'Bb', 5
UNION
SELECT 7, 'Bb1', 6
UNION
SELECT 8, 'Bb2', 6
) A
; WITH TempTable AS
(SELECT ID, 0 AS Depth, Label, ParentID FROM #tmpTable WHERE ParentID IS NULL
UNION ALL
SELECT A.ID, B.Depth + 1, A.Label, A.ParentID FROM #tmpTable A JOIN TempTable B ON A.ParentID = B.ID)
SELECT REPLICATE('-', Depth) + Label FROM TempTable ORDER BY ID
0
PsychotextAuthor Commented:
Damn, that works great... but I've just realised that I'm stuck with SQL Server 2000 for this one.  Any way around that limitation?
0
Adam MenkesC# ASP.NET DeveloperCommented:
Okay, I have some code that works. This assumes your table is called testcat (just rename it where appropriate). I insert records into a table variable (which I like better than temp tables).

Note that my select statements should be removed. I just put them there so you can see the results at various steps.
--select * from testcat

DECLARE @testCat TABLE (
	ID INT,
	SubCategoryOf INT,
	Name VARCHAR(50),
	Level INT,
	PathText VARCHAR(100),
	Path VARCHAR(100)
)

INSERT INTO @testCat
SELECT ID,SubCategoryOf, Name, NULL, NULL, NULL  FROM testcat

select * from @testcat
DECLARE @cnt INT
DECLARE @ctr INT

SELECT @cnt = COUNT(*) FROM @testcat
UPDATE @testCat
	SET Level = 1, PathText = Name, Path = CAST(ID AS VARCHAR(10))
	WHERE SubCategoryOf IS NULL
SET @ctr = 2
WHILE @ctr <= @cnt BEGIN
	UPDATE @testCat
		SET PathText = REPLICATE('--', @ctr) + Name, 
			Level = @ctr WHERE SubCategoryOf IN (
			SELECT ID from @testCat
			WHERE Level = @ctr - 1)

	SET @ctr = @ctr + 1
END		

SET @ctr = 1
SELECT @cnt = COUNT(*) FROM @testcat
DECLARE @subcatof int
WHILE @ctr <= @cnt BEGIN
	SELECT @subcatof = SubCategoryOf
		FROM @testCat WHERE ID = @ctr
	UPDATE @testCat
		SET Path = (SELECT COALESCE(Path, CAST(@subcatof AS VARCHAR(10))) FROM @testCat WHERE ID = @subcatof) + '.' + CAST(ID as VARCHAR(10))
		WHERE ID = @ctr AND Level > 1
	SET @ctr = @ctr + 1
END		
		
select * from @testcat
ORDER BY path

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Adam MenkesC# ASP.NET DeveloperCommented:
And this is what it looks like ordered by path.

CatPath.PNG
0
PsychotextAuthor Commented:
Excellent, thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.