Need an SQL to create a View

Question

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?

Answer 1

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

Answer 2

    CREATE TABLE #temp
    (
    id INT,
    name VARCHAR(20),
    value VARCHAR(10)

    )

    INSERT INTO #temp VALUES(  1,'dog name','ham')
    INSERT INTO #temp VALUES( 1,'cat name' ,'miau')
    INSERT INTO #temp VALUES(   1,'childs',' 1')
    INSERT INTO #temp VALUES(   2,'dog name','wham')
    INSERT INTO #temp VALUES(   2 ,'cat name','meow')
    INSERT INTO #temp VALUES(   2,'childs','3')



    SELECT * FROM #temp 

    PIVOT (MAX(value) FOR name IN ([dog name],[cat name],[childs])) AS PVT



     DROP TABLE #temp

The Solution is

   SELECT * FROM #temp 

    PIVOT (MAX(value) FOR name IN ([dog name],[cat name],[childs])) AS PVT

Replace

"#temp" with your table name