DECLARE @DateLevel int = 0, @DateLevelUp int, @FTID int = 7

CREATE TABLE #FieldRels
        (
        FTIDBase int,
        FTIDCalc int
        )

INSERT INTO #FieldRels(FTIDBase, FTIDCalc)
SELECT 6, 7 UNION ALL
SELECT 7, 8 UNION ALL
SELECT 7, 12 UNION ALL
SELECT 8, 13

/*This is a table showing the relationship between a load of 'FieldIDs'
So, 7 relates to 6, 8 relates to 7, 12 relates to 7 and 13 relates to 8
I want to be able to pass any number into a procedure (hardcoded as 7 above) and return any fields that that ID relates to - both above it (fields it relates to) 
and below it (fields that relate to it)

I want the data to be returned, in this case, like this

6
--7
----8
------13
----12

Showing that, for example, 8 and 12 relate to 7, but 13, which relates to 8, appears between them.

Here is what I have so far.
*/ 

CREATE TABLE #TempLadder (ID int NOT NULL identity(1,1), FTIDBase int, FTIDCalc int, DateLevel int, FieldDesc varchar(255), ThisOne bit, WhereFrom varchar(20))
 
IF EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDCalc = @FTID) --the field passed in has a parent - work up the chain to the parent
	BEGIN
		--find the fields above the one passed in working up
		;WITH rCTE(FTIDBase, FTIDCalc, DateLevel) AS
		(
		SELECT FTIDBase, FTIDCalc, @DateLevel
		FROM #FieldRels
		WHERE FTIDCalc = @FTID
		UNION ALL
		SELECT e.FTIDBase, e.FTIDCalc, @DateLevel + 1
		FROM #FieldRels e
		INNER JOIN rCTE c ON e.FTIDCalc = c.FTIDBase
		)
		 
		INSERT INTO #TempLadder
		SELECT FTIDBase, FTIDCalc, DateLevel, 'Field Description', 'false' AS [ThisOne], 'first'
		FROM rCTE
		ORDER BY DateLevel DESC --sort DESC as we have worked our way up - to get the one furthest from the field passed in to be first
		 
		SET @DateLevelUp = (SELECT MAX(DateLevel) FROM #TempLadder)
		 
		UPDATE #TempLadder SET DateLevel = @DateLevelUp - DateLevel --because we inserted using Sort DESC, reverse DateLevels
		SET @DateLevel = (SELECT MAX(DateLevel) FROM #TempLadder) + 1
		 

		IF NOT EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDBase = @FTID) --if the field passed in is not a parent itself, the field is at the bottom, so add it.
		BEGIN
		INSERT INTO #TempLadder
		SELECT FTIDBase, FTIDCalc, @DateLevel, 'Field Description', 'true' AS [ThisOne], 'second'
		FROM #FieldRels
		WHERE #FieldRels.FTIDCalc = @FTID

		SET @DateLevel = (SELECT MAX(DateLevel) FROM #TempLadder) + 1
		 
		END 

	END
 

IF EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDCalc = @FTID) AND EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDBase = @FTID) --this field has fields above and fields below
	BEGIN
	 
		INSERT INTO #TempLadder
		SELECT FTIDBase, FTIDCalc, @DateLevel, 'Field Description', 'true' AS [ThisOne], 'third'
		FROM #FieldRels
		WHERE #FieldRels.FTIDCalc = @FTID
		
		SET @DateLevel = (SELECT MAX(DateLevel) FROM #TempLadder) + 1

	END
 

IF EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDBase = @FTID) --the field passed in has a child - work down the chain to furthest child
	BEGIN

		IF NOT EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDCalc = @FTID)
		BEGIN
		 
			INSERT INTO #TempLadder
			SELECT TOP 1 FTIDBase, FTIDCalc, @DateLevel, 'Field Description', 'true' AS [ThisOne], 'fourth'
			FROM #FieldRels
			WHERE #FieldRels.FTIDBase = @FTID
			
			SET @DateLevel = (SELECT MAX(DateLevel) FROM #TempLadder) + 1
		 
		END
	 

		;WITH rCTE(FTIDCalc, FTIDBase, DateLevel) AS
		(
		SELECT FTIDCalc, FTIDBase, @DateLevel
		FROM #FieldRels
		WHERE FTIDBase = @FTID
		UNION ALL
		SELECT e.FTIDCalc, e.FTIDBase, @DateLevel + 1 
		FROM #FieldRels e
		INNER JOIN rCTE d ON e.FTIDBase = d.FTIDCalc
		)
		
		INSERT INTO #TempLadder
		SELECT FTIDBase, FTIDCalc, DateLevel, 'Field Description', CASE WHEN FTIDCalc = @FTID THEN 'true' ELSE 'false' END AS [ThisOne], 'fifth' 
		FROM rCTE
 
	END
 
SELECT * FROM #TempLadder
 

DROP TABLE #TempLadder
DROP TABLE #FieldRels

--this is just your sample data inserted into a temp table

IF OBJECT_ID('tempdb..#TempLadder') IS NOT NULL
DROP TABLE #TempLadder

CREATE TABLE #TempLadder
    (
     ID INT NOT NULL IDENTITY(1,1)
    ,FTIDBase INT
    ,FTIDCalc INT
    ,DateLevel INT
    ,FieldDesc VARCHAR(255)
    ,ThisOne BIT
    ,WhereFrom VARCHAR(20)
    )

INSERT INTO #TempLadder
    (
     FTIDBase
    ,FTIDCalc
    ,DateLevel
    ,FieldDesc
    ,ThisOne
    ,WhereFrom
    )
SELECT 6,7,0,'Base Level Item',0,'first'
UNION ALL
SELECT 6,7,1,'1st Level Item',1,'third'
UNION ALL
SELECT 7,8,2,'2nd Level Item',0,'fifth'
UNION ALL
SELECT 7,12,2,'2nd Level Item',0,'fifth'
UNION ALL
SELECT 8,13,3,'3rd Level Item',0,'fifth'

SELECT
    ID
   ,FTIDBase
   ,FTIDCalc
   ,DateLevel
   ,FieldDesc
   ,ThisOne
   ,WhereFrom
FROM
    #TempLadder

SELECT
     CAST(FieldDesc AS VARCHAR(MAX)) AS FieldDesc
    ,ThisOne
    ,WhereFrom
    ,FTIDBase
    ,FTIDCalc
    ,REPLICATE('0',4-(LEN(DateLevel))) + CAST(DateLevel AS VARCHAR(MAX)) + '.' 
        + REPLICATE('0',4-(LEN(FTIDBase))) + CAST(FTIDBase AS VARCHAR(MAX)) + '.' 
        + REPLICATE('0',4-(LEN(FTIDCalc))) + CAST(FTIDCalc AS VARCHAR(MAX)) + '.' 
     AS SortKey
    ,DateLevel AS HierarchyLevel
FROM
    #TempLadder

FieldDesc	ThisOne	WhereFrom	FTIDBase	FTIDCalc	SortKey		HierarchyLevel
Base Level Item	0	first		6		7		0000.0006.0007.	0
1st Level Item	1	third		6		7		0001.0006.0007.	1
2nd Level Item	0	fifth		7		8		0002.0007.0008.	2
2nd Level Item	0	fifth		7		12		0002.0007.0012.	2
3rd Level Item	0	fifth		8		13		0003.0008.0013.	3

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable
   (
    [ID] [int] NOT NULL
   ,[ItemDesc] [char](250) NULL
   ,[Sort] [int] NULL
   ,[ParentID] [int] NULL
   ,[HLevel] [int] NULL
   ,[LocationID] [int] NOT NULL
   ,PRIMARY KEY (ID)
   )

INSERT INTO #TempTable
SELECT 1,'Food Products',10,0,0,22
UNION
SELECT 2,'Fruit',10,1,1,22
UNION
SELECT 3,'Vegetables',20,1,1,22
UNION
SELECT 4,'Apples',10,2,2,22
UNION
SELECT 5,'Granny Smith',10,4,3,22
UNION
SELECT 6,'Ozark Gold',20,4,3,22
UNION
SELECT 7,'Red Delicious',30,4,3,22
UNION
SELECT 8,'Oranges',20,2,2,22
UNION
SELECT 9,'Valencia',10,8,3,22
UNION
SELECT 10,'Hamlin',20,8,3,22
UNION
SELECT 11,'Squash',10,3,2,22
UNION
SELECT 12,'Gold Acorn',10,11,3,22
UNION
SELECT 13,'Turban',20,11,3,22
UNION
SELECT 14,'Yellow',20,11,3,22
UNION
SELECT 15,'Beans',20,3,2,22
UNION
SELECT 16,'Dry',10,15,3,22
UNION
SELECT 17,'Pinto',10,16,4,22
UNION
SELECT 18,'Navy',20,16,4,22
UNION
SELECT 19,'Green',20,15,3,22
UNION
SELECT 20,'Red Swan',10,19,4,22
UNION
SELECT 21,'Blue Lake',20,19,4,22

;WITH ProductLevels (LocationID,ID,ParentID,HLevel,Hierarchy,SortCode)
AS (
    SELECT
         LocationID
        ,ID
        ,ParentID
        ,0 AS HLevel
        ,CAST('//' + REPLACE(REPLACE(REPLACE(REPLACE(ItemDesc,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS Hierarchy
        ,REPLICATE('0',3-LEN(LocationID))
            + CAST(LocationID AS VARCHAR) + '/' +
            + REPLICATE('0',6-LEN(ROW_NUMBER() OVER (ORDER BY Sort)))
            + CONVERT(VARCHAR(MAX),RIGHT(CONVERT(VARCHAR,ROW_NUMBER() OVER (ORDER BY Sort)),6)) AS SortCode
    FROM
        #TempTable
    WHERE
        ParentID = 0
        AND Sort > 0

    UNION ALL

    SELECT
         t.LocationID
        ,t.ID
        ,t.ParentID
        ,r.HLevel + 1
        ,CAST(r.Hierarchy + '//'+ REPLACE(REPLACE(REPLACE(REPLACE(t.ItemDesc,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS Hierarchy
        ,CONVERT(VARCHAR(MAX),r.SortCode + '/'
            + REPLICATE('0',5-LEN(ROW_NUMBER() OVER (ORDER BY t.Sort)))
            + CAST(r.[HLevel] AS VARCHAR)+RIGHT(CONVERT(VARCHAR,ROW_NUMBER() OVER (ORDER BY t.Sort)),6)) AS SortCode
    FROM
        #TempTable AS t
    INNER JOIN 
        ProductLevels AS r
        ON t.ParentID = r.ID
    WHERE
        t.Sort > 0
  )
SELECT
    LocationID
   ,ID
   ,ISNULL(ParentID,0) AS ParentID
   ,HLevel
   ,Hierarchy
   ,SortCode
FROM
    ProductLevels
ORDER BY
    SortCode
OPTION (MAXRECURSION 0)