Sql query how to show column based on data from a different column?

Firstly, giving links to images is not a good way to share your data. Always do it in such a way that we can easily copy and paste data into a test scenario. Here is an example of how I might do it based on your images. NB This might not exactly match your tables but it does give the same result as your first image.

DECLARE @Students TABLE (	StudentNumber INT,
							StudentSurname NVARCHAR(50),
							StudentFirstNames NVARCHAR(50),
							SchoolYear VARCHAR(10),
							Class NVARCHAR(50)
						);
DECLARE @Subject TABLE (	SubjectCode VARCHAR(10),
							SubjectName NVARCHAR(50),
							Cycle NVARCHAR(50),
							Section NVARCHAR(50)
						);
DECLARE @MarksEntry TABLE (	SubjectCode VARCHAR(10),
							StudentNumber INT,
							AssessmentPeriod NVARCHAR(10),
							SchoolYear VARCHAR(10),
							Class NVARCHAR(50),
							SubjectMaxima INT,
							MarksObtained INT
						);

INSERT INTO @Students (StudentNumber,StudentSurname,StudentFirstNames,SchoolYear,Class) VALUES
(202100001, 'Bauma','John','2020-2021','1ere LIT'),
(202100002, 'Kabalo','Gizelle Aline','2020-2021','1ere LIT'),
(202100004, 'Lugi','Rachele','2020-2021','1ere LIT');

INSERT INTO @Subject (SubjectCode, SubjectName,Cycle,Section) VALUES
('REL932','Religion','Secondaire: Humanitiés', 'LITTERAIRE'),
('MIC528','Microbiologie','Secondaire: Humanitiés', 'LITTERAIRE'),
('EDU447','Education Physique','Secondaire: Humanitiés', 'LITTERAIRE');

INSERT INTO @MarksEntry (SubjectCode,StudentNumber,AssessmentPeriod,SchoolYear,Class,SubjectMaxima,MarksObtained) VALUES
('REL932',202100001,'2è P','2020-2021','1ere LIT',10,2),
('MIC528',202100001,'2è P','2020-2021','1ere LIT',20,4),
('EDU447',202100001,'2è P','2020-2021','1ere LIT',20,9),
('REL932',202100002,'2è P','2020-2021','1ere LIT',10,3),
('MIC528',202100002,'2è P','2020-2021','1ere LIT',20,5),
('EDU447',202100002,'2è P','2020-2021','1ere LIT',20,10),
('REL932',202100004,'2è P','2020-2021','1ere LIT',10,4),
('MIC528',202100004,'2è P','2020-2021','1ere LIT',20,6),
('EDU447',202100004,'2è P','2020-2021','1ere LIT',20,19);

Next, we will look at your query. You are already using Joins but in a very old-fashioned way. Instead of using

From Students,MarksEntry,Subject where Students.StudentNumber= MarksEntry.StudentNumber and MarksEntry.SubjectCode= Subject.SubjectCode and Students.Class= MarksEntry.Class 

You should use an ON clause - there is an explanation of why at Difference between WHERE and ON in SQL to JOIN data[^] and it will look like this

from @Students S
	INNER JOIN @MarksEntry M ON S.StudentNumber=M.StudentNumber and S.Class= M.Class 
	INNER JOIN @Subject SJ ON M.SubjectCode= SJ.SubjectCode 
where
	M.SchoolYear= '2020-2021' 
	and M.Class= '1ere LIT' 
	and M.AssessmentPeriod= '2è P' 

You can read more about different kinds of joins at Visual Representation of SQL Joins[^]

Next, instead of using the full name of the table to qualify which table you want the column from you can use an ALIAS - this makes your code easier to read (and type!) - see A list of SQL best practices[^]. Also, , and this is more subjective advice, if you are using table names or aliases for some columns then use them for all the columns. Your query now looks something like below - but note, to make the next section easier I have put the results into a temporary table. I've also used a lot of whitespace to make the query easier to read

SELECT  
	S.StudentNumber,
	S.StudentSurname,
	S.StudentFirstNames,
	S.SchoolYear,
	S.Class, 
	SJ.Cycle,
	SJ.Section,
	M.SubjectCode,
	M.AssessmentPeriod,
	M.SubjectMaxima,
	MarksObtained, 
	SJ.SubjectName 
into #temp	
from @Students S
	INNER JOIN @MarksEntry M ON S.StudentNumber=M.StudentNumber and S.Class= M.Class 
	INNER JOIN @Subject SJ ON M.SubjectCode= SJ.SubjectCode 
where
	M.SchoolYear= '2020-2021' 
	and M.Class= '1ere LIT' 
	and M.AssessmentPeriod= '2è P'  
order by S.StudentSurname

The next step is to transform your data to look like your second image. For that you need to know about PIVOT. You can read more about that at Simple Way To Use Pivot In SQL Query[^]. Here is an example using the temporary table above

SELECT StudentNumber, StudentSurname, StudentName, [Religion],[Microbiologie],[Education Physique]
FROM
(
	select StudentNumber, StudentSurname, StudentFirstNames as StudentName, MarksObtained, SubjectName
	from #temp
) SRC
PIVOT 
(
	SUM(MarksObtained) FOR SubjectName in ([Religion],[Microbiologie],[Education Physique],[Chimie],[Mathématiques])
) PVT

Note that I added some extra subjects. If you don't know what the subjects are going to be, or want to avoid any that might not be relevent then you will have to use DYNAMIC SQL. You can read more about using Dynamic SQL with Pivot at Processing Loops in SQL Server[^]

DECLARE @listSubjects VARCHAR(MAX) = null;
WITH SubjectName AS
(    SELECT distinct  Subject.SubjectName AS SchoolSubject from Subject
)SELECT  @listSubjects = COALESCE(@listSubjects+'],[' ,'') + 
CAST(SchoolSubject AS Varchar)FROM SubjectName

DECLARE @listSubjects VARCHAR(MAX) = null;
WITH SubjectName AS(    SELECT distinct  Subject.SubjectName AS SchoolSubject from Subject
)SELECT  @listSubjects = COALESCE(@listSubjects+'],[' ,'') + CAST(SchoolSubject AS Varchar)
FROM SubjectName
PRINT '[' + @listSubjects + ']'

SELECT S.StudentNumber,	
S.StudentSurname,	
S.StudentFirstNames,	
S.SchoolYear,	
S.Class, 	
--SJ.Class,	
--SJ.Section,	
M.SubjectCode,	
M.AssessmentPeriod,	
M.SubjectMaxima,	
MarksObtained, 	
SJ.SubjectName 
into #temp	
from Students S	
INNER JOIN MarksEntry M ON S.StudentNumber=M.StudentNumber and S.Class= M.Class 	
INNER JOIN Subject SJ ON M.SubjectCode= SJ.SubjectCode 
where	M.SchoolYear= '2020-2021' 	
and M.Class= '1ere LIT' 	
and M.AssessmentPeriod= '2è P'  
order by S.StudentSurname 

SELECT StudentNumber, StudentSurname, StudentName, ['+@listSubjects+']
FROM
(	select StudentNumber, StudentSurname, StudentFirstNames as StudentName, MarksObtained, SubjectName	
from #temp
) 
SRC
PIVOT 
(	
SUM(MarksObtained) FOR SubjectName in (['+@listSubjects+'])
) PVT order by StudentSurname

DECLARE @listSubjects VARCHAR(MAX) = null;
WITH SubjectName AS
(    
SELECT distinct  Subject.SubjectName AS SchoolSubject from Subject
)
SELECT  @listSubjects = COALESCE(@listSubjects+'],[' ,'') + CAST(SchoolSubject AS Varchar)
FROM SubjectName

SELECT S.StudentNumber,	
S.StudentSurname,	
S.StudentFirstNames,	
S.SchoolYear,	
S.Class, 	
--SJ.Class,	
--SJ.Section,	
M.SubjectCode,	
M.AssessmentPeriod,	
M.SubjectMaxima,	
MarksObtained, 	
SJ.SubjectName 
into #temp	
from Students S	
INNER JOIN MarksEntry M ON S.StudentNumber=M.StudentNumber and S.Class= M.Class 
INNER JOIN Subject SJ ON M.SubjectCode= SJ.SubjectCode 
where	
M.SchoolYear= '2020-2021' 	
and M.Class= '1ere LIT' 	
and M.AssessmentPeriod= '2è P'  
order by S.StudentSurname 

DECLARE @sql NVARCHAR(MAX) = 
N' SELECT StudentNumber, StudentSurname, StudentName, 
(
SELECT distinct  Subject.SubjectName  from Subject
) ' 
SET @sql = @sql + N', FROM
(	
select StudentNumber, StudentSurname, StudentFirstNames as StudentName, MarksObtained, SubjectName	
from #temp 
) SRC 
'SET @sql = @sql +N'PIVOT (SUM(MarksObtained) FOR SubjectName in ([' + @listSubjects + N']) )
'SET @sql = N' AS PVT order by StudentSurname'

Exec sp_executesql @sql