Concatenating multiple rows in a single field.

Oracle11g SELECT Column1, listagg (Column2, ',') WITHIN GROUP (ORDER BY Column2) New_Column_nameFROM table_nameGROUP BY Column1 Oracle9i select column1, rtrim (xmlagg (xmlelement (e, column2 || ',')).extract ('//text()'), ',') new_column_namefrom table_namegroup by column1

Thanks everyone, I think I found the best solution was to do a subquery using a user defined function, since wm_concat() returns only varchar and my data was big clobs, here is the function I used: create or replace FUNCTION get_user_posts ( mUSER_ID IN USERS.USER_ID%TYPE ) RETURN CLOB IS full_text CLOB := null; BEGIN FOR cur_rec IN (SELECT POST_TEXT FROM USER_POSTS WHERE USER_ID = mUSER_ID) LOOP full_text := full_text || ',' || cur_rec.POST_TEXT; END LOOP; RETURN LTRIM(full_text, ','); END get_user_posts;

Select UserID, WM_Concat(distinct postTitle) as PostTitle from ( Select U.userid, P.postTitle from User U inner join Post P on u.userid=p.userid)

Yes it is acheivable.
Following may be useful solution Assume that User,User_posts are two tables containing fields like UserID, UserName and PostTitle.
As per requirement the following SQL query can be written in Oracle10G to fetch desired results in a Single field.
SELECT a~UserId, a~UserName, b~PostTitle FROM User as A INNER JOIN User_Posts as b ON (a~UserId = b~UserId).

User Table { UserId, Name, ...} User_Posts Table { UserPostId, Title, UserId, ...} If you want to write a single query that fetces all the users and their posts in a single field you need to join the two tables   SELECT User.UserId, User.Name, User_Posts.Title FROM User INNER JOIN User_Posts ON (User.UserId = User_Posts.UserId)   The query returns the basic information you need for your requirement.
It returns a row per user post.
So if user A has10 posts it shall return10 rows with UserId set to that of user A.
Do you want to have a single row per user and all the titles in a generated column? Why do you have that kind of requirement? You would need to process this on your own.
I am not aware of any built in aggregate function that does this.
You can collect the result of this query and process it either in a stored procedure or in your code.

I think the following is useful for you:

SELECT User.UserId, User.Name, User_Posts.Title FROM User INNER JOIN User_Posts ON (User.UserId = User_Posts.UserId)

Try to play with WM_CONCAT

You can try the below:

SELECT a.user_name||' '||b.post_title

FROM users a, user_posts b

WHERE a.userid = b.userid