Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
0


CREATE TABLE ITEMS (
    ITEM_NUMBER VARCHAR2(10)
);

INSERT ALL
    INTO ITEMS (ITEM_NUMBER) VALUES ('24-1')
    INTO ITEMS (ITEM_NUMBER) VALUES ('24-2')
    INTO ITEMS (ITEM_NUMBER) VALUES ('24-3')
    INTO ITEMS (ITEM_NUMBER) VALUES ('24-4')
    INTO ITEMS (ITEM_NUMBER) VALUES ('24-5')
SELECT * FROM DUAL;

5 rows affected

CREATE TABLE ITEMS_RELATED (
    MASTER_ITEM VARCHAR2(10),
    RELATED_ITEM VARCHAR2(10)
);
INSERT ALL
    INTO ITEMS_RELATED (MASTER_ITEM, RELATED_ITEM) VALUES ('24-1', '24-2')
    INTO ITEMS_RELATED (MASTER_ITEM, RELATED_ITEM) VALUES ('24-1', '24-3')
    INTO ITEMS_RELATED (MASTER_ITEM, RELATED_ITEM) VALUES ('24-1', '24-4')
    INTO ITEMS_RELATED (MASTER_ITEM, RELATED_ITEM) VALUES ('24-1', '24-5')
    INTO ITEMS_RELATED (MASTER_ITEM, RELATED_ITEM) VALUES ('24-2', '24-1')
    INTO ITEMS_RELATED (MASTER_ITEM, RELATED_ITEM) VALUES ('24-3', '24-1')
    INTO ITEMS_RELATED (MASTER_ITEM, RELATED_ITEM) VALUES ('24-4', '24-1')
    INTO ITEMS_RELATED (MASTER_ITEM, RELATED_ITEM) VALUES ('24-5', '24-1')
SELECT * FROM DUAL;
8 rows affected

select * from ITEMS ;


ITEM_NUMBER


24-1

24-2

24-3

24-4

24-5


select * from ITEMS_RELATED;


MASTER_ITEM
RELATED_ITEM


24-1
24-2

24-1
24-3

24-1
24-4

24-1
24-5

24-2
24-1

24-3
24-1

24-4
24-1

24-5
24-1


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;


ITEM_NUMBER
RELATED_ITEM


24-1
24-2

null
24-3

null
24-4

null
24-5

24-2
24-1

24-3
24-1

24-4
24-1

24-5
24-1

MASTER_ITEM	RELATED_ITEM
24-1	24-2
24-1	24-3
24-1	24-4
24-1	24-5
24-2	24-1
24-3	24-1
24-4	24-1
24-5	24-1

ITEM_NUMBER	RELATED_ITEM
24-1	24-2
null	24-3
null	24-4
null	24-5
24-2	24-1
24-3	24-1
24-4	24-1
24-5	24-1