SQL Infinite Class Classification statement

The original table data is:

Using the WITH keyword here, in the program can also be implemented recursively, but think there is no SQL convenient

With TB (id,name,parentid,sort) as( SelectA.*,convert (Varchar,convert (Varchar,partid) +' -'+convert (varchar,id)) fromTest AwherePartid=0--query all parent nodes UNION allSelectA.*,convert (varchar,sort+' -'+convert (varchar,a.id)) fromTest a INNER join TB B on a.partid=b.id)Select* fromTB ORDER BY sort

View Code

This is the data that queries all the parent nodes, such as to query a child node to change the where condition to the id= child node ID.