The data is like Jane has items pen pencil eraser.

If I write the statement 'select * from item' the output is:

Jane pen
Jane pencil
Jane eraser
Jack box
Jack wiper

I want the output to be like:

Jane pen pencil eraser
Jack box wiper

How to do this?

  • 1
    Does your item table have two columns? Do you want your output to have two columns, one with the name and one with a space separated string of all the items they own? If so, how would you handle items that include a space? Or do you want your output to have 4 columns? Or a dynamic number of columns depending on the input data? Commented Jun 13, 2016 at 4:06

1 Answer 1


You can try as below:

SELECT name,
LISTAGG(item_name, ' ') WITHIN GROUP (ORDER BY item_name) AS item_name 
FROM item 
GROUP BY name; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.