FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE Structure (
  Parent CHAR(20),
  Version int,
  Child CHAR(20),
);

INSERT INTO Structure VALUES 
('Q', 1, 'A'), 
('A', 1, 'B'), 
('A', 2, 'B'), 
('A', 3, 'C'),
('C', 1, 'D'),
('C', 2, 'E');

-- QUERY database

WITH my_structure(root, level, path, article) AS (
    select *
	from (SELECT root = Parent, level = 0, cast('root' as varchar(max)) as path, Parent
				FROM Structure
				where Parent = 'Q' and version = 1
		) a 
  		UNION ALL
    select o.root, level+1, cast(concat(o.path, '/', e.Child) as varchar(max)), e.Child
	from (
      SELECT Parent, Version, Child FROM Structure a
		where a.Version = (
			select max(aa.version) from Structure aa 
			where aa.Parent = a.Parent)

/* Comment out the following three rows and the query will no longer be considered valid */
			union all
      select Parent, Version, Child FROM Structure a
      where 1 = 0
      /*                                                               */
    
    ) e
	INNER JOIN my_structure o 
	ON o.article = e.Parent
)
SELECT *
FROM my_structure a

root	level	path	article
Q	0	root	Q
Q	1	root/A	A
Q	2	root/A /C	C
Q	3	root/A /C /E	E

(6 rows affected)

(4 rows affected)