SQL Server 2017

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

WITH Categories AS (
    SELECT 1 AS ID, 'All' AS Title, 0 AS ParentID, 2 AS Orderby UNION ALL
    SELECT 2, 'Banking', 1, 5 UNION ALL
    SELECT 3, 'USAA Checking', 0, 0 UNION ALL
    SELECT 4, 'USAA Mastercard', 1, 9 UNION ALL
    SELECT 5, 'Medical', 3, 0 UNION ALL
    SELECT 6, 'Jobs', 3, 100 UNION ALL
    SELECT 7, 'Archive', 0, 1 UNION ALL
    SELECT 8, 'Active', 7, 0 UNION ALL
    SELECT 9, 'BoA Amex', 1, 1
),

children AS
    (

SELECT 
         ParentID, 0 AS counter, ID, Title, Orderby,
        CAST(ID AS VARCHAR(500)) AS Path
       FROM Categories
       WHERE ParentID = 0
     UNION ALL
       SELECT 
         d.ParentID, t.counter + 1, d.ID, d.Title, d.Orderby,
         CAST(CAST(t.Path AS VARCHAR(50)) + '.' +
         CAST(ROW_NUMBER() OVER (PARTITION BY d.ParentID ORDER BY d.ID) AS VARCHAR(50)) AS VARCHAR(500))
       FROM children t
       INNER JOIN Categories AS d
            ON d.ParentID = t.ID
     )

SELECT
    ID, Title, Path, Orderby
FROM children;

--SELECT *
--FROM Categories c1
--INNER JOIN Categories c2
--    ON c1.ID = c2.ParentID;

ID	Title	Path	Orderby
1	All	1	2
3	USAA Checking	3	0
7	Archive	7	1
8	Active	7.1	0
5	Medical	3.1	0
6	Jobs	3.2	100
2	Banking	1.1	5
4	USAA Mastercard	1.2	9
9	BoA Amex	1.3	1