Is this possible to achieve using SQL views?

I have a task to transform a table that has no hierarchy into a view of the table where it will be added two columns (id and parent) this is for establishing parent-child hierarchy and using it to build a visualization of a tree. The data is regarding the classification of tumors made by the WHO organization called ICD 10. In my table, there are no categories yet and my task is to create categories and then add the relation pattern following the classification made by WHO

- C00-C97 :
  C00-C75 
  C76-C80 
  C81-C96 
  C97-C97 
- D00-D09 

- D10-D36 

- D37-D48 

The task is to investigate the feasibility through the use of views otherwise we will alter the original table.

An example of a table:

I have tried something like this (don't mind the categories as this is the original classification):

CREATE VIEW diagnosis_cim10_tumors_view AS
SELECT *
FROM diagnosis
WHERE Uri LIKE 'cim10:C%' 
   OR Uri LIKE 'cim10:D0%'
   OR Uri LIKE 'cim10:D[1-3][0-9]%'
   OR Uri LIKE 'cim10:D4[0-8]%'
   OR Uri LIKE 'cim10:D49%';   

Result I want to achieve, note how the 'child2' Uri was not originally present in the table and has to be added via the query (cim10:D00-D09) . root refers to "classification" label which represent the starting point:

Answers

To achieve the desired result, you can use a recursive Common Table Expression (CTE) in your view definition. Here's how you can create the view:

CREATE VIEW diagnosis_cim10_tumors_view AS
WITH RECURSIVE TumorHierarchy AS (
    -- Anchor member: Select the root nodes
    SELECT
        Uri,
        'root' AS id,
        NULL AS parent
    FROM
        diagnosis
    WHERE
        Uri LIKE 'cim10:C%' OR
        Uri LIKE 'cim10:D0%'
    UNION ALL
    -- Recursive member: Select the child nodes
    SELECT
        d.Uri,
        CONCAT('child', ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY d.Uri)) AS id,
        p.id AS parent
    FROM
        diagnosis d
    JOIN
        TumorHierarchy p ON d.Uri LIKE CONCAT(p.Uri, '%')
)
SELECT * FROM TumorHierarchy;

In this query:

• We use a recursive CTE named TumorHierarchy to traverse the tumor classification hierarchy.
• In the anchor member, we select the root nodes by filtering based on the starting patterns 'cim10:C%' and 'cim10:D0%'.
• In the recursive member, we join the CTE TumorHierarchy with the diagnosis table to find the child nodes based on the parent nodes selected in the previous iteration.
• We use the ROW_NUMBER() window function to generate unique IDs for the child nodes within each parent group.
• Finally, we select all columns from the recursive CTE TumorHierarchy to create the view.

This view will recursively generate the parent-child hierarchy for the tumor classification, starting from the root nodes ('cim10:C%' and 'cim10:D0%'). The id column represents the unique identifier for each node, and the parent column represents the parent node of each child node.