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
FROM (
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.
