How can I get a query for this report in sql server?

I have this sql database


And I want to show my data such as following:

Go to the complete details ...