help required with sql query

Question

help required with sql query

hlsc1983

SSCommitted

Points: 1971
More actions
May 10, 2016 at 5:02 am

#316439

i have a table MARKS with the following columns:
Studentid int as foreign key ,
Subjectid int as foreign key,
Grade int,
Gradetype int as foreign key,
Grade_pk int as primary ke
A subject can have up to four grade types. There are four grade types -1,2,3,4.
For each pair of studentid and subjectid , we can have multiple rows. Each such row will have a distinct gradetype( There are four grade types -1,2,3,4)
If i use a simple select query, i will get the grades of all students for a particular subject, but the same student will have more than one rows. I want that all the grades of one student in a particular subject be displayed in the same row. Something like this.
subjectid...........studentid........Grade1.......Grade2.........Grade3.......Grade4
Please provide some help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23009 More actions · Answer 1

hlsc1983 (5/10/2016)
i have a table MARKS with the following columns:
Studentid int as foreign key ,
Subjectid int as foreign key,
Grade int,
Gradetype int as foreign key,
Grade_pk int as primary ke
A subject can have up to four grade types. There are four grade types -1,2,3,4.
For each pair of studentid and subjectid , we can have multiple rows. Each such row will have a distinct gradetype( There are four grade types -1,2,3,4)
If i use a simple select query, i will get the grades of all students for a particular subject, but the same student will have more than one rows. I want that all the grades of one student in a particular subject be displayed in the same row. Something like this.
subjectid...........studentid........Grade1.......Grade2.........Grade3.......Grade4
Please provide some help.

Have a look at PIVOT and cross tabs, they are useful for turning rows into columns.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

hlsc1983 (5/10/2016)
i have a table MARKS with the following columns:
Studentid int as foreign key ,
Subjectid int as foreign key,
Grade int,
Gradetype int as foreign key,
Grade_pk int as primary ke
A subject can have up to four grade types. There are four grade types -1,2,3,4.
For each pair of studentid and subjectid , we can have multiple rows. Each such row will have a distinct gradetype( There are four grade types -1,2,3,4)
If i use a simple select query, i will get the grades of all students for a particular subject, but the same student will have more than one rows. I want that all the grades of one student in a particular subject be displayed in the same row. Something like this.
subjectid...........studentid........Grade1.......Grade2.........Grade3.......Grade4
Please provide some help.

please can you tell us what results you would expect for this sample data set

CREATE TABLE #Marks(

StudentId INTEGER NOT NULL

,SubjectID INTEGER NOT NULL

,Grade INTEGER NOT NULL

);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (100,1000,1);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (100,1000,2);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (100,1000,3);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (100,1000,4);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (100,2000,1);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (100,2000,2);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (200,1000,3);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (200,1000,4);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (200,1000,3);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (200,1000,1);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (200,2000,3);

INSERT INTO #Marks(StudentId,SubjectID,Grade) VALUES (200,2000,1);

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What have you tried so far? Where are you stuck?

This looks suspiciously like homework, and you won't learn anything by having someone write the code for you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Check the following article http://www.sqlservercentral.com/articles/T-SQL/63681/

Also, remember that you have other aggregate functions like MAX and not only what it's used in the article.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

hlsc1983 SSCommitted Points: 1971 More actions · Answer 5

NO its not homework..(if u follow my posts u will realise it)

I used pivot query to get the results .

Select * From

(Select subject_id,student_pk, grade_id , marks From Students_Subjects_junction )

as s

Pivot

( Max(marks) For [grade_id] in ([1], [2],[3],[4]) )

as pvt

any way thanks for your help. i will try that too.