How can DUAL be used for something other than calculating an equation?

I am trying to understand how DUAL, which from my understanding, is used to calculate an equation (SELECT (4+3) FROM DUAL;), can be used create a table as well. Given these two tables, "Items" and "Items_Related":

Items:

Items_Related:

How can the following query:

SELECT ITEMS.ITEM_NUMBER, (SELECT DATABASE.ROWS_TO_HTML (SELECT RELATED_ITEM || FROM ITEMS_RELATED || WHERE ITEMS_RELATED.MASTER_ITEM = ``` || ITEMS.ITEM_NUMBER || ```` || `` || ORDER BY ITEMS_RELATED.RELATED_ITEM`) FROM DUAL) AS RELATED_ITEM FROM ITEMS ;

create this table, where 24-1 in the ITEM_NUMBER field is one merged cell when the table is exported as an Excel spreadsheet, and the rest are all separate:

When I tried to input a test query into oracle SQL developer, such as:

SELECT ('SELECT ITEM_NUMBER' || 'FROM ITEMS') FROM DUAL;

the result was:

'SELECT ITEM_NUMBER.....

SELECT ITEM_NUMBERFROM ITEMS

I am not sure if you really need DUAL, I think you can utilise the two tables ITEMS and ITEMS_RELATED and get the required output, let us know.

Fiddle

SELECT 
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY ir.MASTER_ITEM ORDER BY ir.RELATED_ITEM) = 1
        THEN ir.MASTER_ITEM
        ELSE NULL
    END AS ITEM_NUMBER,
    ir.RELATED_ITEM
FROM 
    ITEMS_RELATED ir
JOIN 
    ITEMS i
    ON ir.MASTER_ITEM = i.ITEM_NUMBER
ORDER BY 
    ir.MASTER_ITEM, ir.RELATED_ITEM;

Output