Categorizing in select statement

Question

So i have this table

id     |   object    |     type
--------------------------------
1      |   blue      |     color
1      |   burger    |     food
2      |   sandwich  |     food
2      |   red       |     color
2      |   coke      |     beverage 
3      |   sprite    |     beverage
3      |   coke      |     beverage
3      |   red       |     color
4      |   bacon     |     food

i have to create a select statement that will show a table with columns id, color, food and beverage. Arranged by ID with their designated things on it.

so my expected result is

id       |   color   |   food   |  beverage
-------------------------------------------
1        |   blue    |   burger |
2        |   red     | sandwich |   coke
3        |   red     |          |   sprite
3        |           |          |   coke
4        |           |  bacon   |

as of now i have this code

Select id as id,
Case When I.Type = 'color' Then I.Object End As color, 
Case When I.Type = 'food' Then I.Object End As food, 
Case When I.Type = 'beverage' Then I.Object End As beverage
From table I
order by id

but the problem with my code is it doesnt group by its ID so it creates multiple rows for every object.

TIA!

Answer 1

Robin: My comment to you (under Tim Biegeleisen's answer) is partially incorrect. There IS a pivot-based solution; however, the "groups" are not by id, but instead they are by id AND rank within your three "categories". For this solution (or ANY solution that does not use dynamic SQL) to work, it is necessary that all the "types" (and their names) be known beforehand, and they must be hardcoded in the SQL query.

NOTE: In this solution, I assumed that for each id, the "objects" within the same "type" are associated to each other based on their alphabetical order (so, for example, for id = 3, "coke" is associated with "red" and "sprite" is associated with NULL, unlike your sample output). I asked you about that right below your Question - if there are additional rules you did not share with us, requiring a different pairing of objects of different types, it may or may not be possible to adapt the solution to meet those additional rules.

EDIT: On closer look, this is pretty much what Aleksej provided, without using the explicit pivot syntax. His solution has the advantage that it would work in older versions of Oracle (before 11.1 where pivot first became available).

QUERY (including test data in the first CTE):

with
     inputs ( id, object, type ) as (
       select 1, 'blue'     , 'color'    from dual union all
       select 1, 'burger'   , 'food'     from dual union all
       select 2, 'sandwich' , 'food'     from dual union all
       select 2, 'red'      , 'color'    from dual union all
       select 2, 'coke'     , 'beverage' from dual union all
       select 3, 'sprite'   , 'beverage' from dual union all
       select 3, 'coke'     , 'beverage' from dual union all
       select 3, 'red'      , 'color'    from dual union all
       select 4, 'bacon'    , 'food'     from dual 
     ),
     r ( id, object, type, rn ) as (
       select id, object, type, row_number() over (partition by id, type order by object)
       from   inputs
     )
select id, color, food, beverage
from   r
  pivot ( max(object) for type in ( 'color'    as color, 'food' as food, 
                                    'beverage' as beverage))
order by id, rn
;

OUTPUT:

  ID COLOR    FOOD     BEVERAGE
---- -------- -------- --------
   1 blue     burger
   2 red      sandwich coke
   3 red               coke
   3                   sprite
   4          bacon

Answer 2

You can try with something like the following:

with test(id, object, type) as
(
    select 1,'blue',    'color' from dual union all
    select 1,'burger',  'food' from dual union all
    select 2,'sandwich','food' from dual union all
    select 2,'red',     'color' from dual union all
    select 2,'coke',    'beverage' from dual union all 
    select 3,'sprite',  'beverage' from dual union all
    select 3,'coke',    'beverage' from dual union all
    select 3,'red',     'color' from dual union all
    select 4,'bacon',   'food' from dual
)
select id,
       max( case when type = 'color'    
              then object
              else null
            end
          ) as color,
       max( case when type = 'food'    
              then object
              else null
            end
          ) as food,
       max( case when type = 'beverage'    
              then object
              else null
            end
          ) as beverage
from (
        select id, object, type, row_number() over ( partition by id, type order by object) row_for_id
        from test
     )
group by id, row_for_id   
order by id, row_for_id

The inner query is the main part, where you handle the case of a single id with many objects of a type; you can modify the ordering by editing the order by object. The external query can be re-written in different ways, for example with a PIVOT; i used the MAX hoping to make it clear.

Answer 3

Try this

I have achieved using pivot clause

select id,object,type from yourtable
pivot
(
     LISTAGG(object, ',') WITHIN GROUP (ORDER BY object)
     for type IN
               (
                  'color'  AS "color",
                  'food'   AS "food",
                  'beverage' AS "beverage"
               )
)
order by id

Answer 4

You are looking for a pivot query. What is challenging about your problem is that, for a given id and type, there can be more than one object present. To handle this, you can first do a GROUP BY query to CSV aggregate objects for a given type using LISTAGG:

SELECT id,
       MAX(CASE WHEN t.type = 'color'    THEN t.object ELSE NULL END) AS color,
       MAX(CASE WHEN t.type = 'food'     THEN t.object ELSE NULL END) AS food,
       MAX(CASE WHEN t.type = 'beverage' THEN t.object ELSE NULL END) AS beverage
FROM
(
    SELECT id,
           LISTAGG(object, ',') WITHIN GROUP (ORDER BY object) AS object,
           type
    FROM yourTable
    GROUP BY id, type
) t
GROUP BY t.id

The inner query first aggregates objects across both id and type, and the outer query is a simple pivot query as you might expect.

Here is a Fiddle which shows an almost identical query in MySQL (Oracle seems to be perpetually broken):

SQLFiddle