Concat own column to parent column recursively in sqlite

Question

My goal is to create a unique ID based on the Location of each record. Below I have my Source table, expected output and what I already tried.

This is my Source table.

ID	Location	Name	ParentID
1	1	Room 1	NULL
2	1	Table 1	1
3	2	Table 2	1
4	1	Chair 1	2
5	2	Chair 2	2
6	9	Room 2	NULL
7	1	Chair 3	3

My goal is to get an output like this:

ID	Location	Name	ParentID	ConcatLocation
1	1	Room 1	NULL	1
2	1	Table 1	1	11
3	2	Table 2	1	12
4	1	Chair 1	2	111
5	2	Chair 2	2	112
6	9	Room 2	NULL	9
7	1	Chair 3	3	121

I've tried https://kimsereylam.com/sqlite/2020/03/13/recursive-query-in-sqlite-with-cte.html but this does not give me the result I want. This was the code I made from that website:

with recursive
    child_record(ID, Name, ParentID, Location, NewLocation) as (
        select S.ID, S.Name, S.ParentID, S.Location, S.Location
        from Source S
        
        UNION
        
        select S.ID, S.Name, S.ParentID, S.Location, S.Location, c.Location || S.Location
        from Source S, child_record c
        where c.ID = S.ParentID
    )
select * from child_record

This however returns me a table with the initial 7 records, followed by 5 records which only concatenated their first parent instead of going to the Parent with ParentID NULL.

ID	Location	Name	ParentID	NewLocation
1	1	Room 1	NULL	1
2	1	Table 1	1	1
3	2	Table 2	1	2
4	1	Chair 1	2	1
5	2	Chair 2	2	2
6	9	Room 2	NULL	9
7	1	Table 3	6	1
2	1	Table 1	1	11
3	2	Table 2	1	12
4	1	Chair 1	2	11
5	2	Chair 2	2	12
7	1	Chair 3	3	21

Answer 1

Start with the rows that have NULL as ParentID and then join the children:

WITH cte(ID, Name, ParentID, Location, ConcatLocation) AS (
  SELECT ID, Location, Name, ParentID, Location
  FROM Source 
  WHERE ParentID IS NULL
  UNION ALL
  SELECT S.ID, S.Location, S.Name, S.ParentID, c.ConcatLocation || S.Location
  FROM Source S INNER JOIN cte c
  ON c.ID = S.ParentID
)
SELECT * FROM cte
ORDER BY ID

See the demo.
Results:

ID	Location	Name	ParentID	ConcatLocation
1	1	Room 1	null	1
2	1	Table 1	1	11
3	2	Table 2	1	12
4	1	Chair 1	2	111
5	2	Chair 2	2	112
6	9	Room 2	null	9
7	1	Chair 3	3	121