Reputation: 227
I have a table with data like this-
ID ParentID ProductTypeName
1 NULL Electronics
200 1 TV
300 200 Plasma
67 NULL KitchenAppliances
78 67 Stoves
82 78 Electric
99 78 Gas
23 200 LED
65 300 LG
66 300 Sony
I would like to get the data in the following format -
ID ParentID ProductTypeName Level Sort(Or Some kind of sort value)
1 NULL Electronics 0 1
200 1 TV 1 110
300 200 LED 2 120
65 300 LG 3 12010
66 300 Sony 3 12020
23 200 Plasma 2 100030
67 NULL KitchenAppliances 0 10000010
78 67 Stoves 1 1000001010
82 78 Electric 2 100000101020
99 78 Gas 2 100000101030
To display data in tree in this format. Here please note the children within each parent are sorted as well. Indentation is giving so as to give a better idea of the result -
Electronics
TV
LED
LG
Sony
Plasma
KitchenAppliances
Stoves
Electric
Gas
This is the query I wrote, but does not seem to be working. The sort number logic seems to be broken. Could someone help with this. Any help appreciated. Thanks.
;WITH cte (ID, ParentID, [Level], [Name], Sort) AS(
SELECT sc1.ID,
NULL,
0,
sc1.Name,
cast(row_number()over(partition by sc1.ParentCategoryID order by sc1.Name) as varchar(max)) as Sort
FROM TableData sc1
WHERE sc1.ID is null
UNION ALL
SELECT sc2.ID,
sc2.ParentID,
g2.[level] + 1,
sc2.Name,
g2.Sort + cast(row_number()over(partition by sc2.ParentCategoryID order by sc2.Name) as varchar(max))Sort
FROM dbo.TableData sc2
INNER JOIN cte g2
ON sc2.ParentID = g2.ID
Upvotes: 4
Views: 7110
Reputation: 5120
You are doing it almost right. The only things I changed are: condition WHERE sc1.ID is null
in non-recursive part of the cte changed to WHERE sc1.ParentID is null
, and the way sort key (path) is calculated:
;WITH cte (ID, ParentID, [Name], Level, SortPath, DisplayPath)
AS(
SELECT sc1.ID, NULL, sc1.Name, 0,
cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varbinary(max)),
cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varchar(max))
FROM dbo.TableData sc1
WHERE sc1.ParentID is null
UNION ALL
SELECT sc2.ID, sc2.ParentID, sc2.Name, g2.Level + 1
g2.SortPath + cast(row_number() over (partition by sc2.ParentCategoryID order by sc2.Name) as binary(4)),
g2.DisplayPath + '.' + cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varchar(10))
FROM dbo.TableData sc2
JOIN cte g2 ON sc2.ParentID = g2.ID
)
select ID, ParentID, Name, DisplayPath
from cte
order by SortPath
As you can see, there are two paths calculated, first one is for sorting of elements and second one is for viewing. In case if path of the tree element is not supposed to be shown anywhere, you may leave only SortPath.
Upvotes: 3