Allow me to transpose rows into columns

I have a table data as shown below.

Name Semester Score John Sem 1 80 Michael Sem 1 90 Dennis Sem 1 65 Peter Sem 1 70 John Sem 2 55 Michael Sem 2 85 Peter Sem 2 95 George Sem 2 85 Michael Sem 3 80 Peter Sem 3 70 George Sem 3 75 Dennis Sem 3 70

I want to execute a query ang get the following resultset.

Name Sem 1 Sem 2 Sem 3 Avg Total John 80 55 67.5 Michael 90 85 80 85 Dennis 65 70 67.5 Peter 70 95 70 78.33 George 85 75 80

Help me to build the query.

Thanks in advance