Need SQL to create a view
I have a table in an oracle database:
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 do the following:
id | dog name | cat name | childs
------------------------------------
1 | ham | miau | 1
2 | wham | meow | 3
Can you help me with sql to do this please?
+3
source to share
2 answers
use this query: I was unable to connect to SQLfiddler
. So 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 read more about PIVOT for oracle here: link
+3
source to share
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
Decision
SELECT * FROM #temp
PIVOT (MAX(value) FOR name IN ([dog name],[cat name],[childs])) AS PVT
Replace
"# temp" with your table name
+3
source to share