How do I get multiple rows output in one line?

Question

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?

• Justin Cave: 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?

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;