0

I have a question and am not sure if this is the correct forum to post it .

I have two tables, StudentTable and CourseTable, where each student takes more than one course.

Example: Student1 takes 2 courses: (C1, C2).
Student2 takes 3 courses (C1, C2, C3).

I need to create a table/view that contains student information from StudentTable, plus all the courses and the score for each course from CourseTable - in one row.

Example: 

Row1= Student1_Id, C1_code, C1_name, C1_Score, C2_code, C2_name, C2_Score  
Row2=
Student2_Id, C1_code, C1_name, C1_Score, C2_code, C2_name, C2_Score, C3_code, C3_name, C3_Score

Since Student1 has just two courses, I should enter NULL in 'Course 3 fields'

My struggle is in the insert statement. I tried the following but it showed an error.

Insert Into Newtable 
( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)

Select
(Select St_ID from StudentTable)
,
(Select C_code,c_name,c_Score 
from Coursetable,SudentTable
where course.Stid =Studet.stid)
,
(Select C_code,c_name,c_Score 
from course ,student
where course.Stid =Studet.stid ),
(Select C_code,c_name,c_Score 
from course ,student
where course.Stid =Studet.stid );

I'm fully aware that the New table/View will break the rules of normalization, but I need it for a specific purpose.

I tried also the PIVOT BY functionality but no luck with it.

FYI, I'm not expert in SQL syntax. I just know the basics.

I will be great full for any helpful suggestions to try.

I added My DB structure so you can have better Idea

First Table is Member table which Represent Students Information .The fields in this table are

member_sk (PrimaryKey), full_or_part_time, gender, age_at_entry, age_band_at_entry, disability, ethnicity,

widening_participation_level, nationality

Second Table is Modules table which include the Courses' scores that Student took .

The fields in this table are

Module_result_k(Primary Key), member_sk(Foreign key to connect to Member table), member_stage_sk ,module_k(Foreign key to connect to Module table), module_confirmed_grade_src, credit_or_result

Third Table is AllModuleInfo which is include general information for each course .The fields in this table are

Module_k (Primary key), module_name ,module_code, Module_credit, Module stage.

The New table that I will create has the following fields

member_sk (PrimaryKey), full_or_part_time, gender, age_at_entry, age_band_at_entry, disability, ethnicity,

widening_participation_level, nationality " This will be retrieved from Member table"

Also will include

Module 1_name ,module1_code, Module1_credit, Module1_ stage, member1_stage_sk , module1_confirmed_grade_src, credit1_or_result

Module 2_name ,module2_code, Module2_credit, Module2_ stage, member2_stage_sk , module2_confirmed_grade_src, credit2_or_result

-

-

-

I will repeat this fields 14 times which is equal to Maximum courses number that any of the students took.

//// I hope now my questions become more clear

Dima
  • 38,860
  • 14
  • 75
  • 115
user3895291
  • 21
  • 1
  • 4
  • 1
    What if a student has 4 courses? or 5? or 17? – Lasse V. Karlsen Jul 31 '14 at 11:24
  • yes this is correct , Students may have diffrent number of courses .The following is my Structure so you can have a clear idea – user3895291 Jul 31 '14 at 12:46
  • yes this is correct , Students may have diffrent number of courses, therefore i THAT NEW TABLE SHOULD HAVE THE MAXIMUM number of courses fields that may any student take, and it will include NULL Values by default .I attached my Structure so you can have a clear idea. – user3895291 Jul 31 '14 at 12:52
  • And **why** do you need/want to have a table/view like this? It's going to be MONUMENTALLY hard to maintain and work with. – Lasse V. Karlsen Jul 31 '14 at 12:53
  • This may be a duplicate. The solution here should work for you.http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – Oliver Jul 31 '14 at 12:58
  • I need it like this for Report purpose – user3895291 Jul 31 '14 at 13:44

0 Answers0