1

I have a table that looks something like this 

StudentNr    Module    MarkAchieved
123          Task1     59
123          Task2     49
123          Exam1     62
854          Task1     71
854          Task2     72
854          Exam1     90
785          Task1     41
785          Task2     53
785          Exam1     60

That I want to be displayed like This

StudentNr    Task1     Task2     Exam1
123          59        49        62
854          71        72        90
785          41        53        60

My problem is I don't know how many Modules there will be

I know how to and have gotten the pivot to work but not with Dynamic columns. How can I Pivot my table with Dynamic columns ?

LegionDev
  • 1,391
  • 4
  • 15
  • 29
  • 2
    Have you ever looked at the [PIVOT syntax](http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php)? – Ben May 30 '14 at 07:58
  • Yes I've Tried http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/ And I can get the pivot to work but not Dynamically – LegionDev May 30 '14 at 08:18
  • That article, if you read it, uses objects that are not part of standard SQL... – Ben May 30 '14 at 09:10
  • Ah I see thanks Ben, I hope it's possible to pivot with Dynamic columns in Oracle... – LegionDev May 30 '14 at 12:57
  • It is... I've closed this as a duplicate of something that tells you how. It is _really_ worth reading the docs though. – Ben May 30 '14 at 13:09

1 Answers1

-1

Here is what your query should look like:

select *
from
(
  select * from t
)
pivot (max(MarkAchieved) for (Module) in ('Task1' as task1, 'Task2' as task2, 'Exam1' as exam1))
;

This gives:

STUDENTNR   TASK1   TASK2   EXAM1
123         59      49      62
785         41      53      60
854         71      72      90

Now, the problem with the PIVOT clause in Oracle 11g is that you have to enumerate explicitly all the possible values... This is a limitation, that the PIVOT XML clause does not have for instance (but it will generate XML... yeah, life is bad sometimes).

Emmanuel
  • 13,935
  • 12
  • 50
  • 72
  • Thanks Emmanuel, but my issue is still That I don't know what modules there will be so I can't hardcode the modules – LegionDev May 30 '14 at 08:21