SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Declare @YourTable Table ([CLASSSTRUCTUREID] int,[CLASSIFICATIONID] varchar(50),[PARENT] int)
Insert Into @YourTable Values 
 (1001,'FLEET',NULL)
,(1002,'LIGHTDUTYVEHICLE',1001)
,(1004,'MEDIUMDUTYVEHICLE',1001)
,(1022,'ACTIVETRANSPORTATION',NULL)
,(1023,'FACILITYWALKWAY',1022)
,(1024,'TRAIL',1022)
,(1085,'SIDEWALK',1022)
,(1091,'SDWRAMP',1085)
 
;with cteP as (
      Select [CLASSSTRUCTUREID]
            ,[Parent] 
	    ,[CLASSIFICATIONID]
	    ,[HIERARCHYPATH] = convert(varchar(500),[CLASSIFICATIONID])
	    ,[HierID]        = convert(hierarchyid,concat('/',[CLASSSTRUCTUREID],'/'))
      From   @YourTable
      Where  [Parent] is null
      Union  All
      Select [CLASSSTRUCTUREID]  = r.[CLASSSTRUCTUREID]
            ,[Parent]            = r.[Parent] 
            ,[CLASSIFICATIONID]  = r.[CLASSIFICATIONID]
	    ,[HIERARCHYPATH]     = convert(varchar(500),concat(p.[HIERARCHYPATH],' \ ',r.[CLASSIFICATIONID]))
	    ,[HierID]            = convert(hierarchyid,concat(p.HierID.ToString(),r.[CLASSSTRUCTUREID],'/'))
      From   @YourTable r
      Join   cteP p on r.[Parent]  = p.[CLASSSTRUCTUREID])
Select [CLASSSTRUCTUREID]
	  ,[Parent]
	  ,[CLASSIFICATIONID]
	  ,[HIERARCHYPATH]
 From cteP A
 Order by [HierID]

CLASSSTRUCTUREID	Parent	CLASSIFICATIONID	HIERARCHYPATH
1001	null	FLEET	FLEET
1002	1001	LIGHTDUTYVEHICLE	FLEET \ LIGHTDUTYVEHICLE
1004	1001	MEDIUMDUTYVEHICLE	FLEET \ MEDIUMDUTYVEHICLE
1022	null	ACTIVETRANSPORTATION	ACTIVETRANSPORTATION
1023	1022	FACILITYWALKWAY	ACTIVETRANSPORTATION \ FACILITYWALKWAY
1024	1022	TRAIL	ACTIVETRANSPORTATION \ TRAIL
1085	1022	SIDEWALK	ACTIVETRANSPORTATION \ SIDEWALK
1091	1085	SDWRAMP	ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP