October 7, 2013 at 1:44 am
I have a table like below
MarkIdClassIdExamIdSubjectCodeRollNumberInternalMark ExternalMarkAcadamicYear
1 11 1 ITA01 3401101 12 85 2013-2014
2 11 1 ITA01 3401102 18 83 2013-2014
3 11 1 ITA01 3401103 21 89 2013-2014
4 11 1 ITA02 3401101 23 86 2013-2014
5 11 1 ITA02 3401102 23 94 2013-2014
My query for getting mark for classwise is below.
ALTER procedure [dbo].[sp_getmarkforclass](@classname varchar(30),@examname varchar(30),@medium varchar(30),@acyear varchar(30))
as
begin
declare @paramlist varchar(max),@query nvarchar(max),@query1 nvarchar(max),@examid int,@classid int,@paramlist1 varchar(max)
select @classid=ClassId from schoolcampus.dbo.Class_details where ClassName=@classname and Medium=@medium and Academicyear=@acyear
select @examid=ExamId from ExamNameSetting_details where ExamName=@examname
set @paramlist=STUFF((select distinct ',[' + SubjectCode + ']' from schoolcampus.dbo.Mark_details where classid=@classid and ExamId=@examid for xml path('')),1,1,'')
set @paramlist1=STUFF((select distinct ',[' + Subjectname +']' from schoolcampus.dbo.SubjectRegistration_details where SubjectCode in (select SubjectCode from Mark_details where ClassId=@classid and ExamId=@examid)for xml path('')),1,1,'' )
set @query=N'select * from(select sb.AdmissionNumber,sa.RollNumber,sc.Name,sr.SubjectName,sa.ExternalMark from schoolcampus.dbo.Mark_details sa inner join schoolcampus.dbo.StudentRollnumber_details sb on sa.RollNumber=sb.RollNumber inner join StudentAdmission_details sc on sc.AdmissionNumber=sb.AdmissionNumber inner join SubjectRegistration_details sr on sr.SubjectCode=sa.SubjectCode ) p PIVOT(Max(ExternalMark)for SubjectName IN ('+@paramlist1+')) AS pvt'
set @query1=N'select * from(select sb.AdmissionNumber,sa.RollNumber,sc.Name,sr.SubjectName,sa.InternalMark from schoolcampus.dbo.Mark_details sa inner join schoolcampus.dbo.StudentRollnumber_details sb on sa.RollNumber=sb.RollNumber inner join StudentAdmission_details sc on sc.AdmissionNumber=sb.AdmissionNumber inner join SubjectRegistration_details sr on sr.SubjectCode=sa.SubjectCode ) p PIVOT(Max(InternalMark)for SubjectName IN ('+@paramlist1+')) AS pvt'
execute(@query)
execute(@query1)
end
This stored procedure return two table for mark as internal mark and external mark. but i want to get the internal and external mark in a single table like below method.
AdmissionNumber Rollnumber Name Tamil|internalMark Tamil|externalMark English|internalmark
like wise above. how can make a query like that.
my output for External mark table is.
AdmissionNumberRollNumberName EnglishMathsScienceSocial ScienceTamil
100 401102 karthick 74 70 66 64 65
101 3401103bharathi 70 65 64 60 60
October 7, 2013 at 2:58 am
Try this
exec (@query + ' UNION ALL' + @query1)
but above will work of bolthh select have same columns type and count.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 7, 2013 at 3:16 am
I tried your method.but it simply through an error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ALLselect'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'p'.
October 7, 2013 at 3:46 am
baskey86 (10/7/2013)
I tried your method.but it simply through an error.Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ALLselect'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'p'.
First see print @query + ' UNION ALL ' + @query1 and check whether the query is getting proper syntax
if yes then exec (@query + ' UNION ALL ' + @query1 )
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply