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?
 
Adam MenkesConnect With a Mentor C# 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
 
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:
And this is what it looks like ordered by path.

CatPath.PNG
0
 
PsychotextAuthor Commented:
Excellent, thank you.
0
All Courses

From novice to tech pro — start learning today.