SQL Server tree structure table query get stitching results

The tree table structure is as follows

IF EXISTS(SELECT *  fromSys.all_objectsWHERE object_id = object_id(N'[dbo]. [Test]') andTypeinch('U'))    DROP TABLE [dbo].[Test]GOCREATE TABLE [dbo].[Test] (  [Id] bigint  IDENTITY(1,1) not NULL,  [TypeName] nvarchar( -) COLLATE Chinese_prc_ci_as not NULL,  [ParentID] bigint  NULL)GO-- ------------------------------Records of Test-- ----------------------------SET Identity_insert [dbo].[Test]  onGOINSERT  into [dbo].[Test]([Id],[TypeName],[ParentID])VALUES(N'1'N'Life Knowledge'N'0')GOINSERT  into [dbo].[Test]([Id],[TypeName],[ParentID])VALUES(N'2'N'Life'N'1')GOINSERT  into [dbo].[Test]([Id],[TypeName],[ParentID])VALUES(N'3'N'Common Sense'N'2')GOSET Identity_insert [dbo].[Test] OFFGO

table structure and data

Results that need to be implemented

There are three solutions for this scenario: cursors, loops, CTE

I. Cursors,Loops

DECLARE @temp nvarchar(MAX)="',@pid bigint=3;  while @pid<>0 BEGIN IF @temp="'     SELECT @temp=TypeName,@pid=ParentID from [dbo].[Test]     WHEREId=@pid; ELSE     SELECT @temp=(TypeName+' -'+@temp),@pid=ParentID from [dbo].[Test]     WHEREId=@pid; END; SELECT @temp  asTypeName;

TwoCTE

; withTree as(Select *  from [Test] whereId=3Union  AllSelectA.*  from [Test]AJoinTree b ona.ID=B.parentid)Select Stuff((Select ' -' +TypeName fromTreeOrder  byId forXML PATH ("')),1,5,"')

CTE syntax

;  [] ]::=[] as  (cte_query_definition)

The following points should be noted when using a CTE:
1, the CTE must be followed directly with the use of the CTE SQL statement (such as SELECT, INSERT, UPDATE, etc.), otherwise, the CTE will be invalidated.

2, the CTE can also be followed with other CTE, but only one with, the middle of multiple CTE separated by commas (,), as follows

; withcte1 as(    Select *  fromTable1whereName like 'abc%'), Cte2 as(    Select *  fromTable2whereId>  -), Cte3 as(    Select *  fromTable3wherePrice<  -)SelectA.*  fromCte1 A, Cte2 B, Cte3 cwherea.ID=b.ID anda.ID=C.id

3. If the expression name of the CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using the CTE, of course, the following SQL statement uses the datasheet or view, as follows

--table1 is a table that actually exists; withtable1 as(    Select *  fromPersonswhereAge<  -)Select *  fromTable1--A common table expression named Table1 is usedSelect *  fromTable1--a data table named Table1 is used

4. The CTE can refer to itself, or it can refer to a pre-defined CTE in the same with clause. Forward references are not allowed.

5. The following clauses cannot be used in CTE_query_definition:
(1) COMPUTE or COMPUTE by
(2) ORDER by (unless the TOP clause is specified)
(3) into
(4) OPTION clause with query hint
(5) for XML
(6) for BROWSE

6. If the CTE is used in a statement that is part of a batch, then the statement before it must end with a semicolon,

Reference links

SQL Server tree structure table query get stitching results