Here is my Store Procedure and my question is how to read the columns value in DataReader or DataSet.
ALTER PROCEDURE StudentProgressReport
BEGIN
SET NOCOUNT ON;
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(r.Exam_Date)
FROM Institute_Student_Results r
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT Student_Roll_No,Student_First_Name,Student_Last_Name,' + @cols + ' from
(
select distinct u.Student_Roll_No,
s.Student_First_Name,s.Student_Last_Name,
u.Marks,e.Examination_Total_Marks,
u.Exam_Date
from Institute_Student_Results u
inner join Institute_Examinations e
on u.Exam_ID=e.Institute_Examination_ID
inner join Student_Master s
on u.Student_ID=s.Student_ID
where e.Institute_Course_Batch_ID=110
and u.Exam_ID=105
and u.Marks is not null
) x
pivot
(
MAX(Marks)
for Exam_Date in (' + @cols + ')
) p '
execute(@query)
END
