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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sql syntax suggestions help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 07/31/2014 :  06:27:31  Show Profile  Reply with Quote
Hello Everyone ,

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

I have two table students and courses which is each student take more than one course .

for example Student1 take 2 courses (C1 , C2).
Student2 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
Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score
Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score

and since Student one just have two courses , I should enter NULL in 'Course 3 fields'

My Struggle is 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,C3_code ,C3_name,C3_score)

(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 will break the rules of normalization ,but I need it for specifc 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 basic.

I will be great full for any helpfull suggestions to try ,
thank you very much.

Aged Yak Warrior

550 Posts

Posted - 07/31/2014 :  17:52:39  Show Profile  Reply with Quote

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

    MAX(CASE WHEN course_num = 1 THEN C_Code END) AS C1_Code,
    MAX(CASE WHEN course_num = 1 THEN C_name END) AS C1_Name,
    MAX(CASE WHEN course_num = 1 THEN C_Score END) AS C1_Score,
    MAX(CASE WHEN course_num = 2 THEN C_Code END) AS C2_Code,
    MAX(CASE WHEN course_num = 2 THEN C_name END) AS C2_Name,
    MAX(CASE WHEN course_num = 2 THEN C_Score END) AS C2_Score,
    MAX(CASE WHEN course_num = 3 THEN C_Code END) AS C3_Code,
    MAX(CASE WHEN course_num = 3 THEN C_name END) AS C3_Name,
    MAX(CASE WHEN course_num = 3 THEN C_Score END) AS C3_Score    
    SELECT Stid, C_code, c_name, s_Score, ROW_NUMBER() OVER(PARTITION BY Stid ORDER BY C_code) AS course_num
    FROM Coursetable
) AS course
    course_num BETWEEN 1 AND 3

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000