Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;
6 rows affected

SELECT *
FROM   items
WHERE  item = 'apple';


ITEM
TYPE


apple
fruit

apple
edible


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


ITEM
FRUIT
VEGETABLE
EDIBLE


apple
TRUE
null
TRUE


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


ITEM
TYPES


apple
edible,fruit