SQL Server league table query problem-CodePudding

Good bosses, now I have three tables, respectively F table, Member list, Score table, including

F table data below

:

Member table data is as follows:

The Score table data is as follows:

Now I want to check out a synthetic form, the format is as follows:

Is there a big help to look at, I thank you very much for the

CodePudding user response:

 
The SELECT M.M Name as' Name ', F.F Name, S.S core into # T_A FROM Score S LEFT JOIN F F ON s. ID=F.F ID LEFT JOIN Member M ON S.M ID=M, MID 


Declare @ SQL varchar (Max) 
Set @ SQL='
Select SQL + SQL=@ @ '[' + rtrim (FName) +']=sum (case FName when ' ' '+ rtrim (FName) +' ' 'then Score else 0 end)' 
The from # T_A group by FName 

Exec (' select name + SQL + @ 'from # T_A group by MName')

CodePudding user response:

To fix ~ ~

 

The SELECT M.M Name as' Name ', F.F Name, S.S core into # T_A FROM Score S LEFT JOIN F F ON s. ID=F.F ID LEFT JOIN Member M ON S.M ID=M, MID 


Declare @ SQL varchar (Max) 
Set @ SQL='
Select SQL + SQL=@ @ '[' + rtrim (FName) +']=sum (case FName when ' ' '+ rtrim (FName) +' ' 'then Score else 0 end)' 
The from # T_A group by FName 

Exec (' select name + SQL + @ 'from # T_A group by the name')