2

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?

0
5

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

4
  • Thank you, very nice solution. – tucomax May 30 at 20:12
  • I would like to ask you a different question, how can I group this by parent_id? – tucomax May 31 at 12:38
  • 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 – nbk May 31 at 13:49
  • Thank you for your comments. – tucomax May 31 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.