I have a SQL Server table with these rows:
| id | path |
|---|---|
| 1 | a |
| 2 | a/b |
| 3 | a/b/c |
| 4 | a/b/c/d |
| 5 | a/b/c/e |
| 6 | a/b/f |
| 7 | a/b/f/g |
| 8 | a/h |
| 9 | a/h/i |
| 10 | a/h/j |
etc...
I need a T-SQL script witch generate a treeview with ul, li tag as nvarchar - like this :
<ul><li>a<ul><li>b<ul><li>c<ul><li>d</li><li>e</li></ul></li></ul><ul></li><li>f<ul><li>g</li></ul></li></ul></li></ul><ul></li><li>h<ul><li>i</li><li>j</li></ul></li></ul></ul></li></ul>
And give these result in a HTML page:
Is it possible to have these result on one single query?
It is totally possible to do it with (non-recursive) CTE (Common Table Expressions, an easier-to-read version of subselects) + window functions (which compare each tree node with the ones displayed before and after it).
Other solutions (see the question's comments) could involve recursive CTEs without window functions.
Here the CTE + window solution:
with
-- Get the final element path, as well as its depth in the tree:
pathelem as
(
select
path,
right(path, charindex('/', reverse('/'+path), 1) - 1) elem,
len(path) - len(replace(path, '/', '')) depth
from t
),
tree as
(
select
*,
--- HTML rendering of the element ---
-- 1. As we ensured that all intermediate levels are present, our element starts a new list (<ul>) if it's a level under its predecessor:
case when coalesce(lag(depth) over ord, -1) < depth then '<ul>' else '' end
-- 2. The element itself, always with its <li>:
+ '<li>'+elem
-- 3. After the element, we close some tags.
+ case depth - coalesce(lead(depth) over ord, -1) -- "How many levels will we shift to the left with the next element?"
-- 3.1. Next element is our child: let the <li> open.
when -1 then ''
-- 3.2. Next element is a sibling: just close ourself.
when 0 then '</li>'
-- 3.3. Next element is 1 or more levels left-er in the tree: close all the <ul> still open until we reach that level.
else
'</li>'
+replicate('</ul></li>', depth - coalesce(lead(depth) over ord, 0))
+case when lead(depth) over ord is null then '</ul>' else '' end -- The last line gets special handling, to close the first line's <ul> not included in an <li>.
end
html
from pathelem
-- Good news: in ASCII, '/' ranks before alpha, so 'a/b' will follow 'a' without 'aa' coming inbetween.
-- Beware it won't work with spaces in paths: in that case order by a replace(path, '/', char(3)).
window ord as (order by path)
)
select string_agg(html, '') from tree;
You can see it running in an DBFiddle (or SQL Server 2017-compatible),
that adds (compared to the simplified version below):
a and a/b/c are in the tree it will add the a/b missing link