5

I have a sql table. the table shows studentID, Subject_Name and Marks. Hope you can understand the data.

Now I need to show it in the front end by displaying studentID vertically across the Y axis and subject_Name horizontally across the X axis. Marks should appear as the table body.

I use php as the server side language.

3
  • 2
    can you just draw the output like you want and make the question meaningful. Put the query and desire output. Commented May 18, 2016 at 4:24
  • Is marks denormalized? Something like A,A+,C string? Commented May 18, 2016 at 4:39
  • can you show us how the current table looks? and how you want it to look? Commented May 18, 2016 at 4:55

1 Answer 1

1

I believe a simply pivot query will give you the result set you want:

SELECT studentID,
    SUM(CASE WHEN Subject_Name = 'CHEMISTRY' THEN Marks ELSE 0 END) AS `CHEMISTRY`,
    SUM(CASE WHEN Subject_Name = 'BIOLOGY'   THEN Marks ELSE 0 END) AS `BIOLOGY`,
    SUM(CASE WHEN Subject_Name = 'ENGLISH'   THEN Marks ELSE 0 END) AS `ENGLISH`,
    SUM(CASE WHEN Subject_Name = 'MATH'      THEN Marks ELSE 0 END) AS `MATH`
FROM students
GROUP BY studentID

You can replace and add/subtract the sample columns I gave with the names of the actual course subjects in your table.

Follow the link below for a working demo:

SQLFiddle

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.