Comments by katela (Top 18 by date)

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

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.

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.

Thanks

Many thanks for your insight. I will try to implement it this way and see the results.
Thanks

Thank you

I am using Winforms

Tanks, seem like this is the way to do it.
I'm not expert in C# especially with posting data to other forms. could u please give me a short example of a su or function that can collect data from my inputs and post it to another form?
thanks, i appreciate.

maybe that is why it doesnt work.
I am not an expert please assist.
Thanks

I think that is why it doesnt work but i am not an expert.
Please assist on how i should do.

Ok when in my solution explorer i right click and add new "HTML page" and put this code it works:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Request for Assistance</title>
</head>
<body>
<div id="content">

Submit Email Request for Assistance

<form method="post" action="http://cgi-bin.myhost.co.za/mailto/mailto.pl">
<div>
Your name:
<input type="text" name="recip" />
</div>

<div>
Your email address:
<input type="text" name="from" />
</div>
<div style="width: 688px">
Subject:
<input type="text" name="subj" />
</div>

<div>
Details about your problem: <br />
<textarea name="body" cols="45" rows="4"></textarea>
</div>

<div>
<input type="submit" value="Submit" />
</div>




</form>
</div>
</body>
</html>


When i click submit, i receive that email. my webhosting company forward the email into my inbox.

But the problem is when i write the same code in an ASP.NET page (a page with aspx extention) inside two <html> tags, it doesnt work, the form doesnt do any thing.
The only reason i want to use ASPX page is because of my master page, i want this page to look also like other pages (menu, links,... because of master page)

Thank you

a html submit button works when inside an html page, but it seems not to work when i put it inside an asp.net page

my webhosting company will forward the form to my inbox email.
they dont allow smtp.