By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table category(nodeid int, parentid int , description text);
truncate table category;
insert into category values(1,0,'root description');
insert into category values(2,1,'sub category description');
insert into category(nodeid,parentid) values(3,2);
insert into category(nodeid,parentid) values(4,3);
insert into category values(5,4,'last description');
select * from category;
| nodeid | parentid | description |
|---|---|---|
| 1 | 0 | root description |
| 2 | 1 | sub category description |
| 3 | 2 | null |
| 4 | 3 | null |
| 5 | 4 | last description |
WITH q
AS
(
select nodeid,parentid,description from category where parentid=0
union all
select c.nodeid,c.parentid,c.description from category c inner join q on q.nodeid=c.parentid
)
select description from q where description is not null;
| description |
|---|
| root description |
| sub category description |
| last description |