Master-Detail show data sql

3505 views asp.net
-1

I'm working with sql server and I have this 3 tables

STUDENTS
| id | student |
-------------
| 1  | Ronald  |
| 2  | Jenny   |

SCORES
| id | score | period | student |
| 1  |   8   |   1    |    1    |
| 2  |   9   |   2    |    1    |

PERIODS
| id | period |
| 1  |   1    |
| 2  |   2    |
| 3  |   3    |
| 4  |   4    |

And I want a query that shows me this

Result
| student | score1 | score2 | score3 | score4 |
| Ronald  |   8    |   9    |  null  |  null  |
| Jenny   |  null  |  null  |  null  |  null  |

As you can see, the number of scores depends of the periods because sometimes it can be 4 o 3 periods.

I don't know if I have the wrong idea or should I make this in the application, but I want some help.

I'm working with ASP.net but I'm focus now in the database

answered question

1 Answer

4

You need to PIVOT your data e.g.

select Y.Student, [1], [2], [3], [4]
from (
  select T.Student, P.[Period], S.Score
  from Students T
  cross join [Periods] P
  left join Scores S on S.[Period] = P.id and S.Student = T.id
) X
pivot
(
  sum(Score)
  for [Period] in ([1],[2],[3],[4])
) Y

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-20

posted this

Have an answer?

JD

Please login first before posting an answer.