Insert Into using Muliple Select

[Sara1]

Hello ,

I have one question that am struggle with and I need help please.
I have two table students and courses which is each student take more than one course .
for example St1 take 2 courses (C1 , C2).
St2 take 3 courses (C1,C2, C3).

I need to create a table that contain student information plus all the courses and the score for each course in one row.

for example St1_Id ,C1_code ,C1_name ,C1_Score ,C2_code C2_name , C2_Score ..etc.

My Struggle in the insert statement
I tried the following but it show an error

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

Select
(Select St_ID from Student)
,
(Select C_code,c_name,c_Score
from course ,student
where course.Stid =Studet.stid and C_code= 'CMP')
,
(Select C_code,c_name,c_Score
from course ,student
where course.Stid =Studet.stid and C_code= 'SYS')
;

May you please help in my inster into stmt
thank you.

[Rabbit]

You shouldn't create a table like this. It breaks the rules of normalization and will make it very difficult to query later on. For more on normalization, you can read our article here: http://bytes.com/topic/access/insigh...ble-structures

If you are doing this just for a report, then it is genrerally a bad idea to create a table just for that purpose. Instead, create a view that will denormalize your data. To get it in that format, you can use the PIVOT BY functionality. You can read more about pivot from the Microsoft documentation here: http://technet.microsoft.com/en-us/l...=sql.105).aspx

[Sara1]

@Rabbit
I know I should do not do this , but I need it in one record so I can apply data mining technique later on it .

[Rabbit]

That's fine, you can create a view using the method I mentioned in my post.

[Sara1]

@Rabbit
Thank you very much for your help .
Although I tried the Pivote bY and seems I can't partition using more than one column

[Rabbit]

No, you can't. You would have to create 2 pivots and join them.