0

I have a table.

+--------+--------+--------+
|stud_no |exam_dt |score   | 
+--------+--------+--------+
|1       |01-01-19|95      |
|1       |01-02-19|92      |
|1       |01-04-19|91      |
|1       |01-05-19|96      |
|1       |01-03-19|90      |
|2       |01-06-19|81      |
|2       |01-04-19|83      |
|2       |01-03-19|90      |
|2       |01-07-19|96      |
|2       |01-02-19|94      |
+--------+--------+--------+

and I wanted it to be like 

+--------+--------+--------+--------+--------+--------+--------+--------+
|stud_no |01-01-19|01-02-19|01-03-19|01-04-19|01-05-19|01-06-19|01-07-19|
+--------+--------+--------+--------+--------+--------+--------+--------+
|1       |95      |92      |90      |91      |96      |        |        |
|2       |        |94      |90      |83      |        |81      |96      |
+--------+--------+--------+--------+--------+--------+--------+--------+

and like this if I put a date range in my where clause ex. 01-01-19 to 01-05-19

+--------+--------+--------+--------+--------+--------+
|stud_no |01-01-19|01-02-19|01-03-19|01-04-19|01-05-19|
+--------+--------+--------+--------+--------+--------+
|1       |95      |92      |90      |91      |96      |
|2       |        |94      |90      |83      |        |
+--------+--------+--------+--------+--------+--------+
Miracle
  • 387
  • 5
  • 31
  • You can do that by using `case statement` for each first day of month. But you can't dynamically show the columns by where clause. You can do this by exporting table to excel and then make this table as `pivot` – Farid Imranov Oct 03 '19 at 08:25
  • @MT0 why marked it as duplicate? :( I think somebody is already trying to answer it – Miracle Oct 03 '19 at 08:43
  • If you know the columns and they are fixed then you can use a `PIVOT` statement; however Oracle does not support having an unknown number of columns or dynamic column names, and if that is the case you would be better performing that transformation in whatever user interface/software you are using to connect to the database. – MT0 Oct 03 '19 at 08:43
  • It is a duplicate because you are asking how to perform a dynamic pivot. (Short answer - Oracle does not support it. Long answer - if you want to hack a solution together using XML output or dynamic SQL in PL/SQL then the method to solve it is in the linked duplicate). – MT0 Oct 03 '19 at 08:45
  • But I think it's too early to mark it as duplicate. :( – Miracle Oct 03 '19 at 08:45
  • 1
    It will still be a duplicate whether it gets marked as such today, tomorrow or next year - if it is a duplicate then there is no such thing as "too early". If you want it re-opened then [edit] your question to demonstrate why what you are doing is not a dynamic pivot (which will mean you need to always return a fixed set of columns for known dates). – MT0 Oct 03 '19 at 08:47
  • Okay sir. Thanks – Miracle Oct 03 '19 at 08:49

0 Answers0