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 |