Using PIVOT for a table in ORACLE 12C

oracleoracle-12cpivot

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.

Best Answer

-- 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.

Related Question