How can I change string output in a hierarchy tree with PostgreSQL?

Question

I have a table like this:

-------------------------------------------------
| id | description | parent_id | cost
--------------------------------------------------
| 1 | Radiology | NULL | 0.00
| 2 | Lab Tests | NULL | 0.00
| 3 | Normal Radiology | 1 | 0.00
| 4 | Resonance | 1 | 100.00
| 1100 | Cerebral Resonance| 4 | 200.00
| 1900 | Blood Tests | 2 | 10.00
| 2044 | Calcium | 2 | 50.00
---------------------------------------------------

I need to generate this kind of output:

Radiology
 -->Normal Radiology
 -->Resonance
 -->Cerebral Resonance with contrast
Lab Test
 --> Blood Test
 --> Calcium

I'm working on PostgreSQL. I've been trying this with a recursive CTE, but I was unable to generate what I like:

WITH RECURSIVE hierarchy AS (
 SELECT id, CAST(description AS TEXT) AS parent_list
 FROM orders
 WHERE parent_id is null
 UNION
 SELECT c.id,
 CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
 FROM orders c
 INNER JOIN hierarchy c2 ON c.parent_id = c2.id )
SELECT id, parent_list
FROM hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

That recursive CTE produces the following nondesirable output:

Radiology
Radiology--> Normal Radiology
Radiology--> Resonance
Radiology--> Resonance --> Cerebral Resonance with contrast
Lab Test
Lab Test --> Blood Test
Lab Test --> Calcium

How can I do it?

Answer 1

You could use somethinng like this

CREATE TABLE orders (
 "id" INTEGER,
 "description" VARCHAR(18),
 "parent_id" VARCHAR(4),
 "cost" DECIMAL(8,2)
);
INSERT INTO orders
 ("id", "description", "parent_id", "cost")
VALUES
 ('1', 'Radiology', NULL, '0.00'),
 ('2', 'Lab Tests', NULL, '0.00'),
 ('3', 'Normal Radiology', '1', '0.00'),
 ('4', 'Resonance', '1', '100.00'),
 ('1100', 'Cerebral Resonance', '4', '200.00'),
 ('1900', 'Blood Tests', '2', '10.00'),
 ('2044', 'Calcium', '2', '50.00');

WITH RECURSIVE hierarchy AS (
 SELECT id, 1 AS rown, CAST(description AS TEXT) AS parent_list, id as parent
 FROM orders
 WHERE parent_id is null
 UNION 
 SELECT c.id
 ,rown + 1 as rown
 ,CAST(repeat(' ', rown) || ' --> ' || c.description as text) as parent_list
 ,parent
 FROM orders c
 INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT id,parent_list FROM hierarchy
ORDER BY parent DESC,rown

 id | parent_list 
---: | :------------------------------
 2 | Lab Tests 
1900 | --> Blood Tests 
2044 | --> Calcium 
 1 | Radiology 
 4 | --> Resonance 
 3 | --> Normal Radiology 
1100 | --> Cerebral Resonance

WITH RECURSIVE hierarchy AS (
 SELECT id, CAST(description AS TEXT) AS parent_list, 1 AS rown, id as parent
 FROM orders
 WHERE parent_id is null
 UNION 
 SELECT c.id
 ,CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
 ,rown + 1 as rwon
 ,parent
 FROM orders c
 INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT id, parent_list
FROM hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

 id | parent_list 
---: | :---------------------------------------------
 2 | Lab Tests 
1900 | Lab Tests --> Blood Tests 
2044 | Lab Tests --> Calcium 
 1 | Radiology 
 3 | Radiology --> Normal Radiology 
 4 | Radiology --> Resonance 
1100 | Radiology --> Resonance --> Cerebral Resonance

db<>fiddle here

• tucomax: Thank you, very nice solution.
• tucomax: I would like to ask you a different question, how can I group this by parent_id?
• nbk: please ask a new question and try to make a resuklt that you want. run a SELECT * FROM hierachy and see what columns you have, see if you can add information to Group by , for example parentid has 1 or 2 thtat us the id from the parent for all child rows, ths you can use but for mich complex SELECT i have to know which result you want, more complex more column you need
• tucomax: Thank you for your comments.