By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (id int, parentId int, name varchar(55));
INSERT INTO categories (id, parentId, name)
VALUES
(1, NULL, 'expense'),
(2, 1, 'food'),
(3, 2, 'grocery'),
(4, 3, 'meat'),
(5, 3, 'fruit'),
(6, 2, 'dining');
WITH RECURSIVE cte AS (
SELECT id, parentId, name, name AS path
FROM categories
WHERE parentId IS NULL
UNION ALL
SELECT c.id, c.parentId, c.name, CONCAT_WS(' > ', t.path, c.name)
FROM categories c
INNER JOIN cte t
ON c.parentId = t.id
)
SELECT *
FROM cte
ORDER BY id;
Records: 6 Duplicates: 0 Warnings: 0
| id | parentId | name | path |
|---|---|---|---|
| 1 | null | expense | expense |
| 2 | 1 | food | expense > food |
| 3 | 2 | grocery | expense > food > grocery |
| 4 | 3 | meat | expense > food > grocery > meat |
| 5 | 3 | fruit | expense > food > grocery > fruit |
| 6 | 2 | dining | expense > food > dining |