how to distribute records to colums

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

Transact-SQL (2005)

how to distribute records to colums

Author

Topic

kingjeremy
Starting Member

10 Posts

Posted - 2009-05-12 : 13:04:24

[students]
studentId, studentName
1, John
2, Jane
...

[courses]
courseId, courseName
1, maths
2, chemistry
...

[courseExamDates]
cExamDateId, courseId, examDate, examNr
1, 1, 2009-01-01, 1
2, 1, 2009-02-01, 2
3, 1, 2009-03-01, 3
4, 1, 2009-04-01, 4
5, 1, 2009-05-01, 5
6, 1, 2009-01-03, 1
7, 2, 2009-02-03, 2
8, 3, 2009-03-03, 3
9, 4, 2009-04-03, 4
10, 5, 2009-05-03, 5
...

[studentCourses] --which student takes what course
sCourseId, studentId, courseId
1, 1, 1
2, 2, 1

[studentExamScores]
sExamScoreId, studentId, cExamDateId, score
1, 1, 1, 90
2, 1, 2, 80
3, 1, 3, 88
4, 1, 4, 75
5, 1, 5, 92
6, 2, 6, 80
7, 2, 7, 90
8, 2, 8, 78
9, 2, 9, 79
10, 2, 10, 84
...

Let's assume each course has 5 exams during a year and each student takes those 5 exams.
I'm trying to build a report table that looks like this

[ExamScoresReport]
studentId, studentName, courseName, firstScore, secondScore, thirdScore, fourthScore, fifthScore
1, John, maths, 90, 80, 88, 75, 92
2, Jane, chemistry, 80, 90, 75, 79, 84
....
what would be the most efficient query to get the report above?
thanks for any help

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-12 : 23:16:44

[code]
declare @students table(studentId int, studentName varchar(32))
insert into @students select 1, 'John' union all select 2, 'Jane'

declare @courses table(courseId int, courseName varchar(32))
insert into @courses select 1, 'maths' union all select 2, 'chemistry'

declare @courseExamDates table(cExamDateId int, courseId int, examDate datetime, examNr int)
insert into @courseExamDates select
1, 1, '2009-01-01', 1 union all select
2, 1, '2009-02-01', 2 union all select
3, 1, '2009-03-01', 3 union all select
4, 1, '2009-04-01', 4 union all select
5, 1, '2009-05-01', 5 union all select
6, 1, '2009-01-03', 1 union all select
7, 2, '2009-02-03', 2 union all select
8, 3, '2009-03-03', 3 union all select
9, 4, '2009-04-03', 4 union all select
10, 5, '2009-05-03', 5

declare @studentCourses table (sCourseId int, studentId int, courseId int)
insert into @studentCourses select 1, 1, 1 union all select 2, 2, 2

declare @studentExamScores table(sExamScoreId int, studentId int, cExamDateId int, score int)
insert into @studentExamScores select 1, 1, 1, 90 union all select
2, 1, 2, 80 union all select
3, 1, 3, 88 union all select
4, 1, 4, 75 union all select
5, 1, 5, 92 union all select
6, 2, 6, 80 union all select
7, 2, 7, 90 union all select
8, 2, 8, 78 union all select
9, 2, 9, 79 union all select
10, 2, 10, 84

select s.studentid,s.studentname, c.coursename,se.score,ce.examDate into #temp
from @students s
inner join @studentCourses sc on sc.studentid = s.studentid
inner join @courses c on c.courseid = sc.courseid
inner join @studentExamScores se on se.studentid = s.studentid
inner join @courseExamDates ce on se.cExamDateId = ce.cExamDateId

--select * from #temp
select studentid,studentname,coursename,
max(case when examdate = '1/1/2009' then score
when examdate = '1/3/2009' then score end) as firstscrore,
max(case when examdate = '2/1/2009' then score
when examdate = '2/3/2009' then score end) as secondscrore,

max(case when examdate = '3/1/2009' then score
when examdate = '3/3/2009' then score end) as thirdscrore,

max(case when examdate = '4/1/2009' then score
when examdate = '4/3/2009' then score end) as fourthscrore,

max(case when examdate = '5/1/2009' then score
when examdate = '5/3/2009' then score end) as fifthtscrore
from #temp
group by studentid,studentname,coursename

drop table #temp
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-12 : 23:18:56

u can use pivot function too
see this link for dynamic cross tabs
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 00:08:43

[code]
DECLARE @students TABLE
(
studentId int,
studentName varchar(32)
)
INSERT INTO @students
SELECT 1, 'John' UNION ALL
SELECT 2, 'Jane'

DECLARE @courses TABLE
(
courseId int,
courseName varchar(32)
)
INSERT INTO @courses
SELECT 1, 'maths' UNION ALL
SELECT 2, 'chemistry'

DECLARE @courseExamDates TABLE
(
cExamDateId int,
courseId int,
examDate datetime,
examNr int
)

INSERT INTO @courseExamDates
SELECT 1, 1, '2009-01-01', 1 UNION ALL
SELECT 2, 1, '2009-02-01', 2 UNION ALL
SELECT 3, 1, '2009-03-01', 3 UNION ALL
SELECT 4, 1, '2009-04-01', 4 UNION ALL
SELECT 5, 1, '2009-05-01', 5 UNION ALL
SELECT 6, 2, '2009-01-03', 1 UNION ALL
SELECT 7, 2, '2009-02-03', 2 UNION ALL
SELECT 8, 2, '2009-03-03', 3 UNION ALL
SELECT 9, 2, '2009-04-03', 4 UNION ALL
SELECT 10, 2, '2009-05-03', 5

DECLARE @studentCourses TABLE
(
sCourseId int,
studentId int,
courseId int
)
INSERT INTO @studentCourses
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2

DECLARE @studentExamScores TABLE(sExamScoreId int, studentId int, cExamDateId int, score int)
INSERT INTO @studentExamScores
SELECT 1, 1, 1, 90 UNION ALL
SELECT 2, 1, 2, 80 UNION ALL
SELECT 3, 1, 3, 88 UNION ALL
SELECT 4, 1, 4, 75 UNION ALL
SELECT 5, 1, 5, 92 UNION ALL
SELECT 6, 2, 6, 80 UNION ALL
SELECT 7, 2, 7, 90 UNION ALL
SELECT 8, 2, 8, 78 UNION ALL
SELECT 9, 2, 9, 79 UNION ALL
SELECT 10, 2, 10, 84

SELECT studentId, studentName, courseName,
[1] AS firstScore,
[2] AS secondScore,
[3] AS thirdScore,
[4] AS forthScore,
[5] AS fifthScore
FROM
(
SELECT s.studentId, s.studentName, c.courseName, ce.examNr, se.score
FROM @students s
INNER JOIN @studentCourses sc ON s.studentId = sc.studentId
INNER JOIN @courses c ON sc.courseId = c.courseId
INNER JOIN @courseExamDates ce ON sc.courseId = ce.courseId
INNER JOIN @studentExamScores se ON s.studentId = se.studentId
AND ce.cExamDateId = se.cExamDateId
) d
pivot
(
MAX(score)
FOR examNr IN ([1], [2], [3], [4], [5])
) p

/*
studentId studentName courseName firstScore secondScore thirdScore forthScore fifthScore
----------- -------------------------------- -------------------------------- ----------- ----------- ----------- ----------- -----------
1 John maths 90 80 88 75 92
2 Jane chemistry 80 90 78 79 84

(2 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kingjeremy
Starting Member

10 Posts

Posted - 2009-05-14 : 16:01:23

pivot function does the trick just fine thank you both very much, bklr and khtan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources