2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.