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
 General SQL Server Forums
 New to SQL Server Programming
 Category SubCategory Question

Author  Topic 

gnidesign
Starting Member

2 Posts

Posted - 2014-04-18 : 19:54:32
I've been trying some examples on line and have not been able to get this to work.

Here is my table followed by what I am trying to output.

Id | ParentId | CategoryName

33 | 0 | Fruits
34 | 0 | Vegetables
35 | 0 | Meats
37 | 33 | Grapes
38 | 33 | Oranges
39 | 33 | Apples
40 | 33 | Bananas
41 | 34 | Celery
42 | 35 | Beef
43 | 35 | Fish
44 | 34 | Carrots
45 | 34 | Onions
46 | 35 | Chicken
47 | 0 | Paper Products
48 | 0 | Electronics

I want to output this for a dropdownlist in C# with ascending categories and ascended subcategories

48 0 Electronics
33 0 Fruits
39 33 -Apples
40 33 -Bananas
37 33 -Grapes
38 33 -Oranges
35 0 Meats
42 35 -Beef
46 35 -Chicken
43 35 -Fish
47 0 Paper Products
34 0 Vegetables
44 34 -Carrots
41 34 -Celery
45 34 -Onions

This is what I have currently, still a long way off :/

SELECT TOP (100) PERCENT fcat.Id AS fcat_id, fcat.CategoryName AS fcat_name, fcat.ParentCategory AS fcat_parent, fsub.Id AS fsub_id,
fsub.CategoryName AS fsub_name, fsub.ParentCategory AS fsub_parent
FROM dbo.ProductCategories AS fcat LEFT OUTER JOIN
dbo.ProductCategories AS fsub ON fcat.Id = fsub.ParentCategory
ORDER BY fcat_name, fcat_id, fsub_name

Bill @ gnidesign

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-04-18 : 21:21:13
Try this:
declare @maxlevels int;
set @maxlevels=2;
with cte
as (select Id
,ParentCategory
,CategoryName
,@levels as Level
,power(100.0,@maxlevels-1)*row_number() over (order by CategoryName) as rn
from dbo.ProductCategories
where ParentCategory=0
union all
select a.Id
,a.ParentCategory
,a.CategoryName
,b.Level-1 as Level
,b.rn+power(100.0,b.Level-2)*row_number() over (order by a.CategoryName) as rn
from dbo.ProductCategories as a
inner join cte as b
on b.id=a.ParentCategory
where a.ParentCategory<>0
)
select Id
,ParentCategory
,CategoryName
from cte
order by rn
,CategoryName
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-21 : 00:22:34
Really very good one from you ..Spent lots of time on this..Got the answer.......If you try to understand it will be easy ,anything mess
ask me...



CREATE TABLE #TABLE(ID INT,ParentID INT,CatogoryName VARCHAR(100))
INSERT INTO #TABLE VALUES(33,0,'Fruits')
,(34,0,'Vegetables')
,(35,0,'Meats')
,(37,33,'Grapes')
,(38,33,'Oranges')
,(39,33,'Apples')
,(40,33,'Bananas')
,(41,34,'Celery')
,(42,35,'Beef')
,(43,35,'Fish')
,(44,34,'Carrots')
,(45,34,'Onions')
,(46,35,'Chicken')
,(47,0,'Paper Products')
,(48,0,'Electronics')
--SELECT * FORM #TABLE
DECLARE @InputString VARCHAR(MAX) = STUFF(( SELECT TOP 100 PERCENT ','+ CAST(t.ID AS VARCHAR(MAX))
FROM #TABLE AS t
WHERE ParentID =0 ORDER BY ParentID,CatogoryName
FOR XML PATH('')
), 1, 1, '')
--SELECT @InputString
DECLARE @startingposition INT=1
DECLARE @parts nvarchar(4000)
DECLARE @Result TABLE (ID nvarchar(4000))
DECLARE @FinalResult TABLE (ID INT,ParentID INT,CatogoryName VARCHAR(1024))
WHILE @startingposition !=0
BEGIN
SELECT @startingposition = CHARINDEX(',',@InputString)
IF @startingposition !=0
SELECT @parts = LEFT(@InputString,@startingposition - 1)
ELSE
SELECT @parts = @InputString
INSERT INTO @Result(ID) VALUES(@parts)
INSERT INTO @FinalResult(ID,ParentID,CatogoryName)
(SELECT * FROM #TABLE WHERE ID = @parts
UNION ALL
SELECT * FROM #TABLE WHERE ParentID = @parts)
--SELECT * FROM @FinalResult
SELECT @InputString = RIGHT(@InputString,LEN(@InputString) - @startingposition)
END
SELECT * FROM @FinalResult



---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
   

- Advertisement -