Converting dynamic rows to column

Question

I have been trying to create a view and convert my rows into columns I have this view

view table name: view_all_grades_final_edit_last

studentID | subjectID  | FirstGrading
1080473    Computer 101  0.00
1080473    History 101   0.00
1080473    Java 101      0.00
1080473    PE 101        0.00
1080473    Science 101   74.85
1111857    Computer 101  0.00
1111857    History 101   0.00
1111857    Java 101      0.00
1111857    PE 101        0.00
1111857    Science 101   69.07

I want the output to be like this:

studentID | Computer 101 | History 101 | Java 101 | PE 101 | Science 101

1080473     0.00           0.00          0.00      0.00        74.75
1111857     0.00           0.00          0.00      0.00        69.07

Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). — Strawberry, Feb 05 '19 at 14:34
(PHPMyAdmin is not relevant to this question - except insofar as it implies that you do indeed have access to application code) — Strawberry, Feb 05 '19 at 14:35

score 0 · Answer 1 · answered Feb 05 '19 at 14:33

0

If the subjectids are known then you can simply do conditional aggregation :

SELECT studentID,
       MAX(CASE WHEN subjectID = 'Computer 101' THEN FirstGrading END), 
       . . . 
       MAX(CASE WHEN subjectID = 'Science 101' THEN FirstGrading END)
FROM view_all_grades_final_edit_last v
GROUP BY studentID;

answered Feb 05 '19 at 14:33

Yogesh Sharma

49,870
5
26
52

It's dynamic, I got it to work now!! – kalabaw usbaw Feb 05 '19 at 14:38
@kalabawusbaw. . . I'd give you one advice, don't edit the question whenever you have a new requirement. For instance your actual problem was on "How to pivot the data", but now you have made edit with new requirement on "How to make a view for Dynamic Pivot". So, that is not valid. So, just rollback your question with older & ask other question with new requirement. – Yogesh Sharma Feb 05 '19 at 15:53
Oops sorry hehe :v – kalabaw usbaw Feb 05 '19 at 16:23

Converting dynamic rows to column

1 Answers1