SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Sara1
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
Row2=
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
(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.

ScottPletcher
Constraint Violating Yak Guru

USA
421 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 )

SELECT
    Stid,
    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    
FROM (
    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
WHERE
    course_num BETWEEN 1 AND 3
GROUP BY
    Stid
ORDER BY
    Stid

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000