0

Initial table students

NAME     School    Class
 John     Hs      English
 Steve    Hs      Maths
 Matthew  Hs      Science
 Jim      Hs      History

Output Needed: I need the query to auto pick up Name column data from initial table and change it to column headers in output and since the names will continuously change i cannot hard code the names using simple pivot query. I am new to pivot queries so I wanted to request if someone can help me out. Thank You.

School  John     Steve  Matthew  Jim
Hs      English  Maths  Science  History

Here's what i tried: *Note( I am trying to use this query in Oracle Sql Developer to achieve the output format)

declare 
        sqlqry clob;
        cols clob;
  begin 
  select listagg('''' || NAME || ''' as "' || NAME || '"', ',') within group   (order by NAME)
  into cols
  from (select distinct NAME from Students);
  sqlqry := 
  '
   select * from(select NAME,SCHOOL,CLASS from Students)
   pivot(MAX(CLASS) FOR NAME IN (' || cols || ')
   )';
   execute immediate sqlqry;
   end;
Unknown
  • 41
  • 3
  • Please update your question tags. Microsoft `sql-server` conflicts with `oracle-sqldeveloper`. You are trying to achieve a "dynamic pivot", there are several examples on this site already such as [this one](https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot). – Sander Aug 28 '20 at 21:09
  • Thank You i will update the tags and I did try the things in the link that you provided but it didnt work for me – Unknown Aug 28 '20 at 21:13
  • Please add your attempt to the question as well as the error or unexpected result you encountered. A full [minimal, reproducable example](https://stackoverflow.com/help/minimal-reproducible-example) is very much preferred. – Sander Aug 28 '20 at 21:17
  • Hi Sander, I have added my attempt into the question. Thank you for all the help that you have been providing – Unknown Aug 30 '20 at 16:14
  • Does this answer your question? [Dynamic Pivot Query using Sql Developer Oracle](https://stackoverflow.com/questions/63694461/dynamic-pivot-query-using-sql-developer-oracle) – Barbaros Özhan Oct 01 '20 at 21:05

0 Answers0