CODEWITHSUNDEEP
sqloracle-databaserowcol
Hokins
Hokins

Reputation: 1

How can row values in a specific Col can be converted into Col headings

I have a table in oracle DB containing records of students who attempted multiple papers as shown in picture:-

Main table showing records

Now I want to get the results of one student in each row as shown in the picture below:-

Desired output from SQL query

Upvotes: 0

Views: 36

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

One option is to use pivot:

SQL>   SELECT main_event_id event,
  2           student_id studid,
  3           MAX ("'MCQs - T/F'_TM") AS "MCQs - T/F total marks",
  4           MAX ("'MCQs - T/F'_MO") AS "MCQs - T/F obtained marks",
  5           MAX ("'Short question'_TM") AS "Short question total marks",
  6           MAX ("'Short question'_MO") AS "Short question obtained marks"
  7      FROM (
  8              SELECT * FROM exam
  9           )
 10           PIVOT (MAX (total_marks) tm, MAX (marks_obtained) mo
 11                 FOR sub_event_name
 12                 IN ('MCQs - T/F', 'Short question'))
 13  GROUP BY main_event_id, student_id
 14  ORDER BY main_event_id, student_id;

 EVENT  STUDID MCQs - T/F total marks MCQs - T/F obtained marks Short question total marks Short question obtained marks
------ ------- ---------------------- ------------------------- -------------------------- -----------------------------
     1     101                     10                         2                         20                            12
     1     102                     10                         8                         20                            16

Another one is to use conditional aggregation:

SQL>   SELECT main_event_id event,
  2           student_id studid,
  3           MAX (CASE WHEN sub_event_name = 'MCQs - T/F' THEN total_marks END)
  4              "MCQs - T/F total marks",
  5           MAX (CASE WHEN sub_event_name = 'MCQs - T/F' THEN marks_obtained END)
  6              "MCQs - T/F obtained marks",
  7           MAX (CASE WHEN sub_event_name = 'Short question' THEN total_marks END)
  8              "Short question total marks",
  9           MAX (CASE WHEN sub_event_name = 'Short question' THEN marks_obtained END)
 10              "Short question obtained marks"
 11      FROM exam
 12  GROUP BY main_event_id, student_id
 13  ORDER BY main_event_id, student_id;

 EVENT  STUDID MCQs - T/F total marks MCQs - T/F obtained marks Short question total marks Short question obtained marks
------ ------- ---------------------- ------------------------- -------------------------- -----------------------------
     1     101                     10                         2                         20                            12
     1     102                     10                         8                         20                            16

SQL>

If you ask whether you can do that dynamically - yes, using XML, but result is then in XML format.

Upvotes: 1

Related Questions