0

I have a database with the following design

Standards:

ID   | Standard   |    Mentor
1    | X          |    Mr. M
2    | IX         |    Mrs. S

Subjects:

ID   |  Subject   | StandardID
1    |  Adv. Math |   1
2    |  Math      |   2
3    |  Eng       |   2

Students:

ID   |  Name   | StandardID
1    | A       |   1
2    | b       |   2

Marks :

ID   |  SubjectID   | StudentID  | Marks
1    |   1          |   1        |  10
2    |   2          |   2        |  9
3    |   3          |   2        |  8

Now I want to display the data as shown based on the standard

For X standard:

StudentID  | Name    |   Adv. Math
1          | A       |     10

For IX standard:

StudentID  | Name    |   Math | Eng
2          | b       |    9   | 8

Note :

  1. I am running sql server 2019
  2. Different standards have different number of subjects
  3. The number of subjects and their names per standard are unknown
  4. The number of standards are unknown

Is there any way to get the above results from a query? or do I need to change the table schema? If so then what needs to be changed to get the above results

Prithvi Emmanuel Machado
  • 35
  • 7
  • Does this answer your question? [How to pivot column values into columns?](https://stackoverflow.com/questions/12287240/how-to-pivot-column-values-into-columns) – Thom A Jul 21 '22 at 13:17
  • 1
    Dose this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A Jul 21 '22 at 13:18
  • 1
    Before you go down this path, consider what will consume or use any potential resultset. Dynamic sql requires advanced skills to implement - using that resultset can be equally difficult. – SMor Jul 21 '22 at 13:24
  • Larnu https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query works thanks – Prithvi Emmanuel Machado Jul 21 '22 at 14:23

0 Answers0