Sql, get category tree for values from one table, names and ids

Question

What I need
I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category).

Example On web there is product with category tree.

In table "category" I have parent category id as 0, and other children categories ids are defined by numbers as this.

How can I in sql show all categories tree in one field? For example.

Úvod > Bydlení > Barvy a Laky > Vnitřní barvy

What is the simplest way to do that?

DBMS = PostgreSQL ver. 10.9.

Thank you very much.

BR.

Edited, updated, for ids main category id is not present.

By Akina solution.

with recursive

cte as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte.category_name, ' > ', s_category.category_name)
from s_category,cte
where cte.category_id = s_category.category_parent
),

cte2 as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte2.category_id, ' : ', s_category.category_id)
from s_category,cte2
where cte2.category_id = s_category.category_parent
)

select
cte.category_name as tree_category,
cte2.category_id as tree_id,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand

from
s_product

left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join cte on s_product.category_id = cte.category_id
left join cte2 on s_product.category_id = cte2.category_id

where
s_product.product_id = '133471'

Answer 1

Example.

Structure and data:

CREATE TABLE tree (id INT, parent INT, name VARCHAR);

INSERT INTO tree VALUES
(1,0,'root'),
(2,1,'branch 1'),
(3,1,'branch 2'),
(4,2,'branch 3'),
(5,2,'branch 4'),
(6,4,'branch 5'),
(7,6,'branch 6'),
(8,3,'leaf 1'),
(9,5,'leaf 2'),
(10,7,'leaf 3');

Query:

WITH RECURSIVE cte AS ( SELECT t1.id, t1.parent, t1.name
                        FROM tree t1
                        LEFT JOIN tree t2 ON t1.id = t2.parent
                        WHERE t2.parent is null
                        UNION ALL
                        SELECT t1.id, t1.parent, CONCAT(t1.name, ' > ', t2.name)
                        FROM tree t1
                        JOIN cte t2 ON t1.id = t2.parent 
                      )
SELECT name
FROM cte
WHERE parent = 0;

Output:

                    name
---------------------------------------------------------
root > branch 2 > leaf 1
root > branch 1 > branch 4 > leaf 2
root > branch 1 > branch 3 > branch 5 > branch 6 > leaf 3

fiddle

---

UPDATE:

WITH recursive cte AS ( SELECT t1.category_id, t1.category_parent,    t1.category_name::text
                    FROM s_category t1
                    LEFT JOIN s_category t2 ON t1.category_id = t2.category_parent
                    WHERE t2.category_parent is null
                    UNION ALL
                    SELECT t1.category_id, t1.category_parent,     CONCAT(t1.category_name, ' > ', t2.category_name)
                    FROM s_category t1
                    JOIN cte t2 ON t1.category_id = t2.category_parent 
                  )
select 

cte.category_name AS category,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand,
-- as category,
s_category.category_id as categoryID
-- as hierarchy,
-- as params,
-- as name,
-- as value,
-- as labels

from

public.s_product
left join s_product_image on s_product.product_id =             s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join cte on s_product.category_id = cte.category_id
left join s_producer on s_product.producer_id = s_producer.producer_id

where

s_product.product_id='133471'
and s_pricelist_generate

UPDATE2:

Alternative CTE - producing URL for each node.

WITH RECURSIVE 
cte AS ( SELECT id, parent, name
         FROM tree
         WHERE parent = 0
       UNION ALL
         SELECT tree.id, tree.parent, CONCAT(cte.name, ' > ', tree.name)
         FROM tree, cte
         WHERE cte.id = tree.parent
       )