MG_1 MG_1 - 1 month ago 7x
SQL Question

How link son with his father by sql statement

Image for SQL Table data

I have table in SQL server I want show the data like:

  1. Smith

  2. Johnson Smith

  3. Williams Smith

  4. Brown Johnson Smith


IF SQL Server (going by the posted image)

Declare @Table table (ID int,CLevel int,CParent int ,Name varchar(50))
Insert into @Table values 
(2,2,1    ,'Johnson'),
(3,2,1    ,'Williams'),
(7,3,2    ,'Brown')

;with cteHB (ID,CParent,Lvl,Name,PathName) as (
    Select  ID
           ,PathName = cast(Name as varchar(500)) 
     From   @Table 
     Where  CParent is null
     Union  All
     Select cteCD.ID
           ,PathName = cast(concat(cteCD.Name,' ',cteHB.PathName) as varchar(500))
     From   @Table cteCD 
     Join   cteHB on cteCD.CParent = cteHB.ID)
Select A.ID
 From cteHB A


ID  CParent  Lvl  Name       PathName
1   NULL     1    Smith      Smith
2   1        2    Johnson    Johnson Smith
3   1        2    Williams   Williams Smith
7   2        3    Brown      Brown Johnson Smith