Oracle 23c

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

CREATE TABLE CUSTOMERS (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(255)
)
CREATE TABLE DETAILS (
    ID NUMBER,
    DETAIL VARCHAR2(255),
    VALUE VARCHAR2(255),
    CONSTRAINT fk_customer FOREIGN KEY (ID) REFERENCES CUSTOMERS(ID)
)
begin

INSERT INTO CUSTOMERS (ID, NAME) VALUES
(5, 'John Smith');
INSERT INTO CUSTOMERS (ID, NAME) VALUES
(17, 'Jane Doe');


INSERT INTO DETAILS (ID, DETAIL, VALUE) VALUES
(5, 'Street', 'Elm St.');
INSERT INTO DETAILS (ID, DETAIL, VALUE) VALUES
(5, 'Post_code', '12345');
INSERT INTO DETAILS (ID, DETAIL, VALUE) VALUES
(5, 'Number', '17');
INSERT INTO DETAILS (ID, DETAIL, VALUE) VALUES
(17, 'Telephone', '09999999');
INSERT INTO DETAILS (ID, DETAIL, VALUE) VALUES
(17, 'Fax', '088888888888');

end;
/
1 rows affected

select * from customers


ID
NAME


5
John Smith

17
Jane Doe


select * from details


ID
DETAIL
VALUE


5
Street
Elm St.

5
Post_code
12345

5
Number
17

17
Telephone
09999999

17
Fax
088888888888


SELECT 
    c.ID, 
    c.NAME,
    LISTAGG(d.DETAIL || ': ' || d.VALUE, ' ') WITHIN GROUP (ORDER BY d.DETAIL DESC) AS DETAILS
FROM 
    CUSTOMERS c
JOIN 
    DETAILS d ON c.ID = d.ID
GROUP BY 
    c.ID, c.NAME;


ID
NAME
DETAILS


5
John Smith
Street: Elm St. Post_code: 12345 Number: 17

17
Jane Doe
Telephone: 09999999 Fax: 088888888888


SELECT 
    c.ID, 
    c.NAME,
    LISTAGG(d.DETAIL || ': ' || d.VALUE, ' ') AS DETAILS
FROM 
    CUSTOMERS c
JOIN 
    DETAILS d ON c.ID = d.ID
GROUP BY 
    c.ID, c.NAME;


ID
NAME
DETAILS


5
John Smith
Street: Elm St. Post_code: 12345 Number: 17

17
Jane Doe
Telephone: 09999999 Fax: 088888888888

ID	DETAIL	VALUE
5	Street	Elm St.
5	Post_code	12345
5	Number	17
17	Telephone	09999999
17	Fax	088888888888

ID	NAME	DETAILS
5	John Smith	Street: Elm St. Post_code: 12345 Number: 17
17	Jane Doe	Telephone: 09999999 Fax: 088888888888