DECLARE @stu_Personal_Details TABLE (PersonalDetail_Id INT IDENTITY,Student_Id INT,First_Name VARCHAR(20),Last_Name VARCHAR(20))
INSERT INTO @stu_Personal_Details
SELECT 1,'First Name','Last Name' UNION ALL
SELECT 2,'First Name1','Last Name1' UNION ALL
SELECT 3,'First Name2','Last Name2'

SELECT * FROM @stu_Personal_Details


DECLARE @stu_Acadamic_Details TABLE (Student_Id INT IDENTITY,Admission_No INT ,Medium_Id INT,Class_Id INT,Section_Id INT,Roll_No INT)
INSERT INTO @stu_Acadamic_Details
SELECT 1,1,1,1,10001 UNION ALL
SELECT 2,1,1,1,10002 UNION ALL
SELECT 3,1,1,2,10004

SELECT * FROM @stu_Acadamic_Details


DECLARE @Subjects TABLE (Subject_Id INT IDENTITY, Class_Id INT, Medium_Id INT, Section_Id INT, Description VARCHAR(20))
INSERT INTO @Subjects
SELECT 1,1,1,'Sub1' UNION ALL
SELECT 1,1,1,'Sub2' UNION ALL
SELECT 1,1,1,'Sub3' UNION ALL
SELECT 1,1,1,'Sub4' UNION ALL
SELECT 1,1,1,'Sub5' UNION ALL
SELECT 1,1,1,'Sub6' UNION ALL
SELECT 1,1,2,'Sub1'
SELECT * FROM @Subjects


DECLARE @ExamTypes TABLE (ExamType_Id INT IDENTITY, Class_Id INT, Medium_Id INT, Section_Id INT, Description VARCHAR(20))
INSERT INTO @ExamTypes
SELECT 1,1,1,'First Unit'
SELECT * FROM @ExamTypes

DECLARE @Marks TABLE (MarksDetailsId INT IDENTITY, Student_Id INT, ExamType_Id INT, Subject_Id INT, Marks INT)
INSERT INTO @Marks
SELECT 1,1,1,1 UNION ALL
SELECT 1,1,2,2 UNION ALL
SELECT 1,1,3,3 UNION ALL
SELECT 1,1,4,4 UNION ALL
SELECT 1,1,5,5 UNION ALL
SELECT 1,1,6,6 UNION ALL
SELECT 2,1,1,6 UNION ALL
SELECT 2,1,2,5 UNION ALL
SELECT 2,1,3,4 UNION ALL
SELECT 2,1,4,3 UNION ALL
SELECT 2,1,5,2 UNION ALL
SELECT 2,1,6,1
SELECT * FROM @Marks

AdmissionNo     RollNo     Name                  Sub1     Sub2     Sub3    Sub4   Sub5    Sub6   Total 
1 		10001 	   First Name Last Name  1	  2        3       4      5       6      21
2 		10002      First Name1LastName1  6        5        4       3      2       1      21

quote:

---

Originally posted by chravikishore

but the subjects may differ every time that means no of subjects may differ for different classes

---

quote:

---

Originally posted by chravikishore

can u help me for my case

---

SELECT	M.Student_Id, SUB.Description, AD.Roll_No, M.Marks
		, SUM(M.Marks) OVER (PARTITION BY M.Student_Id) AS 'TotalMarks'
INTO #T_Final
FROM @Marks M
INNER JOIN @stu_Personal_Details PD ON PD.Student_Id = M.Student_Id
INNER JOIN @stu_Acadamic_Details AD ON AD.Student_Id = M.Student_Id
INNER JOIN @Subjects SUB ON SUB.Subject_Id = M.Subject_Id 
				AND SUB.Section_Id = AD.Section_Id
				AND SUB.Medium_Id = AD.Medium_Id
WHERE M.ExamType_Id = 1
		AND AD.Section_Id = 1
		AND SUB.Class_Id = 1
		
DECLARE @StrCols VARCHAR(MAX)
DECLARE @StrSql VARCHAR(MAX)

SELECT	@StrCols = '', 
		@StrSql= ''

SELECT @StrCols = @StrCols + ', ISNULL(MAX( CASE WHEN [Description] = ''' + [Description] + ''' THEN Marks END), 0) AS "' + [Description] + '"'
FROM	(
		SELECT DISTINCT [Description] FROM #T_Final
		) A

PRINT @StrCols

SELECT @StrSql = '
		        SELECT Student_Id' + @StrCols + '
					, TotalMarks
			FROM #T_Final
			GROUP BY Student_Id, TotalMarks'

EXEC (@StrSql)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"