How to get multiple rows output in one line

oracleoracle-11g-r2

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?

Best Answer

You can try as below:

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

Related Solutions

Convert Log table days in a state

select item, 
       sum(duration),
       moved_to
from (
  select item, 
         lead(move_date)  over (partition by item order by move_date) - move_date as duration,
         moved_to
  from movement         
) t
group by item, moved_to
order by item, moved_to;

SQLFiddle example: http://www.sqlfiddle.com/#!4/dff2c/1

Comparing values between two tables

Assuming that sql_id has a unique or primary key constraint, you can do this with FULL JOIN (or LEFT JOIN if there are no new "inserts" in table B, or just JOIN if there are no inserts and no deletions):

SELECT 
    COALESCE(a.sql_id,b.sql_id) AS sql_id,
    CASE WHEN a.operation = b.operation THEN 'matched' 
         WHEN a.operation <> b.operation THEN 'value changed'
         WHEN b.operation IS NULL THEN 'a deleted'
         WHEN a.operation IS NULL THEN 'b inserted' 
    END AS status,
    a.operation AS a_operation,
    b.operation AS b_operation
FROM 
  a FULL JOIN b
      ON a.sql_id = b.sql_id ;

Best Answer

Related Solutions

Convert Log table days in a state

Comparing values between two tables

Related Question