how to use CTE or subquery in sql

Question

Hi,

 

here is my sql table...

 

 

I want output somthing like this...

 

 

How to do this using sql join or CTE or subquery.

can any one help me to solve this query?