select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select query

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2009-11-03 : 10:28:12

hi
i have three tables.

StudentMaster - Regno(PK), Name

MarkEntry - EntryId(PK), Regno(FK - refers studentmaster), Subid(FK- refers subject master), Mark

SubjectMaster - Subid(PK), Subjectname, Maxmark, Minmark

I need the output in the following manner

Sno Regno Name S1name S1mark S2name S2mark S3name S3mark s4name S4mark
1 1001 abc Lang 90 Maths 97 Scien 86 social 67
2 1002 pqr Bio 45 Phy 40 Mat 37 Che 43

here i refer s1,s2... as subject1, subject2 etc.

Can anyone help me.

Thanks in advance

Regards
SSM

sql-programmers
Posting Yak Master

190 Posts

Posted - 2009-11-18 : 17:30:38

Try to use this query

DECLARE @Query NVARCHAR(MAX),
@Count int,
@iC int

set @Count=(
SELECT top 1 count(Subjectname)
FROM SubjectMaster
INNER JOIN MarkEntry
ON SubjectMaster.Subid=MarkEntry.Subid
INNER JOIN StudentMaster
ON StudentMaster.Regno=MarkEntry.Regno
group by StudentMaster.Regno
order by count(Subjectname) desc
)

set @iC=1

set @Query=''
set @Query='create table T(RegNo int,Name varchar(50)'

WHILE @Count > 0
BEGIN
SET @Query = @Query + ',[S' + convert(varchar,@iC)+ '] varchar(100),[M' + convert(varchar,@iC) + '] int'
set @Count=@Count-1
set @iC=@iC+1
END

set @Query=@Query+')'
execute (@Query)

DECLARE @IQuery NVARCHAR(MAX),
@IQuery1 NVARCHAR(MAX),
@RegNo as int,
@Name as varchar(50),
@Subject as varchar(100),
@Mark as int,
@Flag as int,
@ID int

set @iC=1
set @Flag=0
set @ID=0

DECLARE Cur CURSOR FOR
SELECT StudentMaster.Regno, [Name], Subjectname, Mark
FROM SubjectMaster
INNER JOIN MarkEntry
ON SubjectMaster.Subid=MarkEntry.Subid
INNER JOIN StudentMaster
ON StudentMaster.Regno=MarkEntry.Regno
OPEN Cur

FETCH NEXT FROM Cur INTO @RegNo, @Name, @Subject, @Mark
WHILE @@FETCH_STATUS = 0
BEGIN

if @ID <> @RegNo
begin
if @ID<>0
begin
set @Flag=1
end
set @ID = @RegNo
end

if @Flag=0 and @iC=1
begin
SET @IQuery ='insert into T(RegNo,Name,[S' + convert(varchar,@iC)+ '],[M' + convert(varchar,@iC) + ']'
SET @IQuery1 ='values(' + convert(varchar,@RegNo) + ','''+ @Name + ''','''+ @Subject + ''',' + CONVERT(varchar,@Mark)
set @iC=@iC+1
end
else if @Flag=0
begin
SET @IQuery = @IQuery+',[S' + convert(varchar,@iC)+ '],[M' + convert(varchar,@iC) + ']'
SET @IQuery1 = @IQuery1+ ','''+ @Subject + ''',' + CONVERT(varchar,@Mark)
set @iC=@iC+1
end
else
begin
SET @IQuery = @IQuery+')'
SET @IQuery1 = @IQuery1+')'

execute(@IQuery + ' ' + @IQuery1)
Set @Flag=0
set @iC=1

SET @IQuery ='insert into T(RegNo,Name,[S' + convert(varchar,@iC)+ '],[M' + convert(varchar,@iC) + ']'
SET @IQuery1 ='values(' + convert(varchar,@RegNo) + ','''+ @Name + ''','''+ @Subject + ''',' + CONVERT(varchar,@Mark)
set @iC=2
end

FETCH NEXT FROM Cur INTO @RegNo, @Name, @Subject, @Mark
END
CLOSE Cur
DEALLOCATE Cur

SET @IQuery = @IQuery+')'
SET @IQuery1 = @IQuery1+')'
execute(@IQuery + ' ' + @IQuery1)
select * from T
drop table T

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 01:00:45

will you certain on number of subjects always? or else you want to use dynamic sql for this. see below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources