Help with TSQL aggregation and concatenation

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

Transact-SQL (2000)

Help with TSQL aggregation and concatenation

Author

Topic

GMK
Starting Member

1 Post

Posted - 2005-06-22 : 16:42:32

I am aware that the kind of formatting shouldn't be done on the database, but I woul like some help in the following query...

create table Schedule(studentID int, course varchar(5), section int, period int)
create table Marks (StudentID int, Course varchar(5), Section int, MarkingPeriod int, Mark int)

insert into marks values(151,'E7',3,1,1)
insert into marks values(151,'E7',3,2,55)
insert into marks values(151,'E7',3,3,85)
insert into marks values(151,'E8',1,1,50)
insert into marks values(151,'E8',1,2,50)
insert into marks values(213,'JE2',1,1,80)
insert into marks values(213,'JE2',1,2,80)
insert into marks values(151,'E9',3,1,50)

INSERT INTO SCHEDULE values(151,'E7',3,5)
INSERT INTO SCHEDULE values(151,'E8',1,1)
INSERT INTO SCHEDULE values(151,'EST1',2,2)
INSERT INTO SCHEDULE values(213,'JE2',1,3)
INSERT INTO SCHEDULE values(151,'E9',3,5)
go

The following query

select StudentID, Period,
convert(varchar,Course)
+ ' ' + convert(varchar,Section )
+ ' ' + coalesce(convert(varchar,Mark1),'-')
+ '/' + coalesce(convert(varchar,Mark2),'-')
+ '/' + coalesce(convert(varchar,Mark3),'-') Result
from
(
select s.StudentID, s.Period, s.Section, s.Course,
max(case when m.MarkingPeriod = 1 then m.Mark else null end) Mark1,
max(case when m.MarkingPeriod = 2 then m.Mark else null end) Mark2,
max(case when m.MarkingPeriod = 3 then m.Mark else null end) Mark3
from Schedule s left join Marks m
on s.Course = m.Course
and s.Section = m.Section
group by s.StudentID, s.Period, s.Section, s.Course
) sm

gives me the following results

StudentID Period Course
151 1 E8 1 50/50/-
151 2 EST1 2 -/-/-
151 5 E7 3 1/55/85
151 5 E9 3 50/-/-
213 3 JE2 1 80/80/-

I want the above rows to be transposed to columns (and grouped).
What is the best way to implement the above.
Thanks.

Help Desk
Starting Member

10 Posts

Posted - 2005-06-23 : 15:08:12

It will be easiest (and possibly neccesary) to have to insert these values into a table or temporary table to be able to group on them.

www.dotdesk.com Help Desk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources