Latish Sehgal's Blog

Concatenating Rows in a Table Into a Single String Using Sql

Today i came across a new scenario in one of my applications (that talks to an oracle database) where we had to concatenate a field from multiple rows in a table into a single string. While some suggested that string manipulation could be done inside the web application code after retrieving all the data, my alert (read over-caffeinated) brain was sure that there must be a way to tackle this at the database level by manipulating the sql query. 30 minutes with the Google gods proved me correct. I have created a brief test case with the solution below, if you run into a similar situation.

To create the test table, run CreateTable.sql (.91KB) after saving it to your system.

Our table looks like this, and our goal is to create a concatenated string of all the names.

The sql uses SYS_CONNECT_BY_PATH() (used for hierarchial queries), and the final sql is

SELECT LTRIM(SYS_CONNECT_BY_PATH(Employee_Name, ‘,’),’,’) Concatenated_Names


SELECT Employee_Name, ROW_NUMBER() OVER (order by Employee_Name) rownumber, COUNT(*) OVER () cnt

FROM (SELECT Employee_Name FROM Employee)

) data

WHERE rownumber = cnt

START WITH rownumber = 1

CONNECT BY PRIOR rownumber = rownumber-1;

Below is the result of the query.

-Latish Sehgal