Nested Lists...So close! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Nested Lists...So close!

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2001-12-12 : 22:58:36

Okay, I have gotten my code to this point(I have no idea if it's the most efficient way so far, I'm open to suggestions). There's just one problem. I can't seem to figure out how to sort a tree structure as follows:

 
Main Category
  SubCategories
    SubCategories
etc...

Joe Celko's method was perfect up until this point, but I'm sure it's more a limit of me, than the nested list design. If I try to sort by CatLevel, then it won't maintain the heirarchy. If I sort on LBound, then it just puts it in a heirarchial order, but does not alphabetize. Sort on Category name, and you lose both CatLevel, and the heirarchy.


SELECT 
	C1.CategoryId,
	C1.CategoryName,
	C1.CategoryDescription,
	C1.LBound,
	C1.UBound,
	Levels.CatLevel	
FROM 
	tblCategories AS C1, 
	tblCategories AS C2,
	(
	SELECT COUNT(C3.CategoryId) AS CatLevel, C3.CategoryId
	FROM tblCategories C3, tblCategories C4
	WHERE C3.LBound BETWEEN C4.LBound AND C4.UBound
	GROUP BY C3.CategoryId
	) AS Levels	
WHERE 
	C1.LBound BETWEEN C2.LBound AND C2.UBound
		AND C2.CategoryId = @CategoryId
		AND C1.CategoryId = Levels.CategoryId

chaugner
Starting Member

9 Posts

Posted - 2001-12-13 : 08:48:56

SELECT CategoryName = case
when CatLevel = 1 then CategoryName
when CatLevel = 2 then ' ' + CateoryName

Hope that will point you in the right direction.

Chris

Blastrix
Posting Yak Master

208 Posts

Posted - 2001-12-13 : 10:08:03

Thanks, that will definitely help, that was one of my other questions. I was just going to do it in ASP, but this way will be much more efficient.

I'm still looking for an answer on how to get it all sorted properly. Hopefully someone will have some insight on that today.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-13 : 12:10:41

quote:

SELECT CategoryName = case
when CatLevel = 1 then CategoryName
when CatLevel = 2 then ' ' + CateoryName

Or rather more simply,

REPLICATE(' ', CatLevel - 1) + CategoryName

Blastrix
Posting Yak Master

208 Posts

Posted - 2001-12-14 : 15:07:01

Thanks for the tip Arnold. I still need to figure this out. Someone out there has to know!

This is SQLTeam.com! I have never posted a question here that someone couldn't answer, and now, I have posted two questions on nested lists, and no one can answer them?! I think I'm going to pass out from shock!

chaugner
Starting Member

9 Posts

Posted - 2001-12-14 : 15:43:10

quote:

quote:

SELECT CategoryName = case
when CatLevel = 1 then CategoryName
when CatLevel = 2 then ' ' + CateoryName

Or rather more simply,

REPLICATE(' ', CatLevel - 1) + CategoryName

I was always looking for a better way .. now I now .. thanks

Edited by - chaugner on 12/14/2001 15:44:04

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources