using PIVOT for a table in ORACLE 12C

Question

I got the results as below after querying the multiple tables in our database (oracle database 12c enterprise edition).

TABLE 1

but we would like to see the table as:

TABLE 2

there are more than 100 rows in the table and also when I write the PIVOT command in the query window, it doesn't highlighted with any colour as other commands do. so please help me out of this scenario.

Answer 1

-- sample data
with data as
(
 select 34 as id, 1 as an_id, 'FIRST NAME' as title, 
 'ANDY' as full_title from dual union all
 select 34 as id, 1 as an_id, 'LAST NAME' as title, 
 'COOL' as full_title from dual union all
 select 34 as id, 1 as an_id, 'FIRST & LAST' as title, 
 'ANDY COOL' as full_title from dual union all
 select 34 as id, 2 as an_id, 'FIRST NAME' as title, 
 'SCHUMACK' as full_title from dual union all
 select 34 as id, 2 as an_id, 'LAST NAME' as title, 
 'LAST' as full_title from dual union all
 select 34 as id, 2 as an_id, 'FIRST & LAST' as title, 
 'SCHUMACK LAST' as full_title from dual union all
 select 32 as id, 1 as an_id, 'FIRST NAME' as title, 
 'MARIO' as full_title from dual union all
 select 32 as id, 1 as an_id, 'LAST NAME' as title, 
 'SANTI' as full_title from dual union all
 select 32 as id, 1 as an_id, 'FIRST & LAST' as title, 
 'MARIO SANTI' as full_title from dual
)
-- query
select * from data
pivot (min(full_title) for title in ('FIRST NAME', 'LAST NAME', 'FIRST & LAST'))
order by id desc, an_id
;
 ID AN_ID 'FIRST NAME' 'LAST NAME' 'FIRST & LAST
---------- ---------- ------------- ------------- -------------
 34 1 ANDY COOL ANDY COOL 
 34 2 SCHUMACK LAST SCHUMACK LAST
 32 1 MARIO SANTI MARIO SANTI 

Note that PIVOT requires an aggregate function, I could have used for example MAX as well.