how to return multiple values if a condition satisfy in oracle sql

Question

how can we return multiple values if a condition is satisfy for ex we can't create or edit the existing table

table= ITEMS

        ITEM  |.     TYPE
------------------------------------------
apple,         | fruit
onion,        | vegetable
mango          |fruit
apple          |edible         |
mango          | edible
onion         |edible

select case when item IN ('apple','mango') then ('fruit')
when item IN ('onion') then ('vegetable')
when team In ('apple','mango','onion') then ('edable')
end 
from ITEAMS
where item='apple'

output

fruit edible

AS apple is a fruit and edible please advice how could I achieve this in oracle sql thanks in advance

Answer 1

Based on this example:

create table items (item varchar2(10), type varchar2(10));

insert into items values ('apple','fruit');
insert into items values ('onion','vegetable');
insert into items values ('mango','fruit');
insert into items values ('apple','edible');
insert into items values ('mango','edible');
insert into items values ('onion','edible');

You could get the output you are looking for with this query:

select item, listagg(type,' ') as attributes
from items
group by item;

But you might consider redesigning the table so that you don't have different types of attributes (edible vs fruit) stored in the same column.

Answer 2

Given the sample data:

CREATE TABLE ITEMS (ITEM, TYPE) AS
SELECT 'apple', 'fruit'     FROM DUAL UNION ALL
SELECT 'onion', 'vegetable' FROM DUAL UNION ALL
SELECT 'mango', 'fruit'     FROM DUAL UNION ALL
SELECT 'apple', 'edible'    FROM DUAL UNION ALL
SELECT 'mango', 'edible'    FROM DUAL UNION ALL
SELECT 'onion', 'edible'    FROM DUAL;

If you want the values a rows then you can simply use the WHERE clause to filter the rows and do not need to use a CASE expression:

SELECT *
FROM   items
WHERE  item = 'apple';

Which outputs:

ITEM	TYPE
apple	fruit
apple	edible

---

If you want the values as columns then you can PIVOT the table:

SELECT *
FROM   items
       PIVOT (
         MAX('TRUE') FOR type IN (
           'fruit' AS fruit,
           'vegetable' AS vegetable,
           'edible' AS edible
         )
       )
WHERE  item = 'apple';

Which outputs:

ITEM	FRUIT	VEGETABLE	EDIBLE
apple	TRUE	null	TRUE

---

If you want the values as a comma-delimited string then in Oracle 11g you can GROUP BY and use LISTAGG:

SELECT item,
       LISTAGG(type, ',') WITHIN GROUP (ORDER BY type) AS types
FROM   items
WHERE  item = 'apple'
GROUP BY item;

Which outputs:

ITEM	TYPES
apple	edible,fruit

Oracle 10g does not support LISTAGG but there are similar alternatives.

fiddle