Click here to Skip to main content
14,879,577 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
How can I make an sql query so that I can get the data listing only the students with no duplication and the score marks under each subject, with the name of the subject as column name. The subjects names are not the same, they differ from classes, so they have to be read dynamically from the subjectName column based on the query condition

What I have tried:

This is the query I have so far:

SQL
SELECT  Students.StudentNumber,  Students.StudentSurname,Students.StudentFirstNames,
Students.SchoolYear,Students.Class, Cycle,Section,MarksEntry.SubjectCode,MarksEntry.AssessmentPeriod,
MarksEntry.SubjectMaxima,MarksObtained, Subject.SubjectName 
from Students,MarksEntry,Subject 
where Students.StudentNumber= MarksEntry.StudentNumber and 
MarksEntry.SubjectCode= Subject.SubjectCode and 
Students.Class= MarksEntry.Class and MarksEntry.SchoolYear= '2020-2021' and MarksEntry.Class= '1ere LIT' and MarksEntry.AssessmentPeriod= '2è P'  
order by Students.StudentSurname


It displays data this way: https://i.stack.imgur.com/bKpVX.jpg[^]

But I need my query to display data this way: https://i.stack.imgur.com/3tCb7.jpg[^]

How can I accomplish this?
Thanks a lot for your time.
Posted
Updated 20-Apr-21 22:12pm
v3

Without knowing your DB layout and what links them together we can't really help you - but the chances are that what you want to look at is a JOIN: SQL Joins[^]

These allow you to pull information from one table, along with related information from other tables - which sounds like its what you need.
   
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.
SQL
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
SQL
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
SQL
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
SQL
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
SQL
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[^]
   
Comments
katela 20-Apr-21 10:40am
   
Hi CHill60,
Thanks a millions for the answer, this is exactly what I needed and I didn't have a clue how to get here.
I tried many scenarios, I didn't know about PIVOT.
So far your query is generating the records as required, I will also read more about the Dynamic SQL with Pivot.
Thanks.
CHill60 20-Apr-21 11:00am
   
My pleasure! If you get stuck do come back with another question
katela 21-Apr-21 4:18am
   
I tried to follow this article: Processing Loops in SQL Server[^] to generate the subjects dynamically.
With this statement, I can generate the list of subjects in correct format in bracket separated by commas just as required:

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


Tried to replace the [Religion],[Microbiologie],[Education Physique] by my variable: @listSubjects, but the subject column returns NULL:

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



I tried also this way but with no success:

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'


Anybody can post my mistakes?
Thanks a lot.
CHill60 21-Apr-21 4:48am
   
I posted the solution in my comment on the solution you have now removed. When I ran that code it produced the solution you require. You need to execute the SQL with
Exec sp_executesql @sql
katela 21-Apr-21 5:54am
   
Thank you very much.
Apologies for asking the follow-up question in the wrong section.
I received your first message in my notification email.
Thanks a lot for your time to assist me. I was able to get the results with the Exec sp_executesql @sql

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900