how to combine the result of one column to another

  • A+
Category:Languages

I am trying to create a student database but i am stuck up the given requirement.

I was given to create 2 tables one with students register number , subjects ans total and another table with student_name , total, rank. i can understand that here we need to use the student name separately and combine it later but the part that got me struck in the student subjects may vary means if student no :1 is having 3 subjects means student no : 2 may have 2 and student no: 3 may have 5 and based on this we have to put ranking order

CREATE TABLE Student (StudentID int, StudentName varchar(6), Details varchar(1));       INSERT INTO Student (StudentID, StudentName, Details)   VALUES (1, 'John', 'X'), (2, 'Paul', 'X'), (3, 'George', 'X'), (4, 'Paul', 'X');  CREATE TABLE Subject (SubjectID varchar(1), SubjectName varchar(7));      INSERT INTO Subject   (SubjectID, SubjectName) VALUES ('M', 'Math'), ('E', 'English'), ('H', 'History');  CREATE TABLE Mark (StudentID int, SubjectID varchar(1), MarkRate int);      INSERT INTO Mark  (StudentID, SubjectID, MarkRate) VALUES (1, 'M', 90), (1, 'E', 100), (2, 'M', 95), (2, 'E', 70), (3, 'E', 95), (3, 'H', 98), (4, 'H', 90), (4, 'E', 100); 

I need 2 outputs 1st one is

ID  |subjects |marks ---------------------------------------------------------- 1     maths     98 1     science   87 1     social    88 2     maths     87 2     english   99 3     maths     96 3     evs       100 3     social     88 3     history    90 

and the second table as

NO  |name |total|rank ---------------------------------------------------------- 1   xxx     123     1 2   yyy     456     2 3   zzz     789      3 

I need output like this for n number of entries

 


select StudentID ,SubjectName ,MarkRate from  #Mark a join #Subject b on a.SubjectID=b.SubjectID 

output

StudentID   SubjectName MarkRate 1              Math       90 1              English    100 2              Math       95 2              English    70 3              English    95 3              History    98 4              History    90 4              English    100 

Second Query

  with cte as     (     select a.StudentID,StudentName,sum(MarkRate)MarkRate  from #Student a join #Mark B on a.StudentID=b.StudentID     group by a.StudentID,StudentName     )     select *,rank() over( order by MarkRate desc) as rn  from cte 

output

StudentID   StudentName MarkRate    rn 3             George    193         1 4             Paul      190         2 1             John      190         2 2             Paul      165         4 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: