Combine multiple Rows to a Column – Oracle

Post Info: 2,418 views 0 Comments Post a comment

Oracle SQL to convert multiple rows of result set to a single column.

CREATE TABLE FRUITS ( NAME VARCHAR2(20));

INSERT INTO FRUITS VALUES ('APPLE');
INSERT INTO FRUITS VALUES ('BANANA');
INSERT INTO FRUITS VALUES ('CHERRY');

SELECT SUBSTR (SYS_CONNECT_BY_PATH (NAME , ','), 2) FRUITS_LIST
FROM (SELECT NAME , ROW_NUMBER () OVER (ORDER BY NAME ) RN,
COUNT (*) OVER () CNT
FROM FRUITS)
WHERE RN = CNT
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1;

Results from the above query:

FRUITS_LIST
————————–
APPLE,BANANA,CHERRY

1 row selected.

Return to top

Leave a Reply

 

Are you finished?

Return to top