Sql syntax suggestions help - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sql syntax suggestions help

Author

Topic

Sara1
Starting Member

1 Post

Posted - 2014-07-31 : 06:27:31

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
Aged Yak Warrior

550 Posts

Posted - 2014-07-31 : 17:52:39

[code]

--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

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources