Search code examples
sqloraclesql-view

Need an SQL to create a View

I have an table in a oracle database like that:

   id | name      | value
   -----------------------
   1  | dog name  | ham
   1  | cat name  | miau
   1  | childs    | 1
   2  | dog name  | wham
   2  | cat name  | meow
   2  | childs    | 3

and I want to make a view like this:

 id  |  dog name | cat name | childs
------------------------------------
  1  |  ham      | miau     | 1
  2  |  wham     | meow     | 3

can you help me with the sql to do that please?

Solution

  • use this query: I was unable to connect to SQLfiddler. So, do check the code and tell me what it returned.

    select *
from
(
    SELECT id, name, value FROM table A)
    pivot
    (
        max(value) for name in ('dog name', 'cat name', 'childs')
    )
order by id

    You can learn more about PIVOT for oracle from here: link