Combining two rows in a view

I have created a view for reporting. Im basically just joining a few
tables. It is for a University so the results shows students names and
the credits they are currently taking and the school code (There is 3
Colleges under one ownership)
The problem is some students attend two colleges and appear twice,
one for each enrollment. For example
FName LName Credits SchoolCode
John Smith 12 1468
John Smith 4 1469
I need to combine these results so it would look like this
John Smith 16 1468
This is not for all students just certain ones. I would like to do
this in the view if possible. Any help is appreciated.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...50.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=904750Looks like you want to return just one of the school codes? In that case,
just group the data by student, and aggregate the measures:
SELECT StudentID, FName, LName, SUM(Credits) AS TotalCredits,
MIN(ScheelCode) AS MinSchoolCode
FROM ViewName
GROUP BY StudentID, FName, LName;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"TheCount" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_904750_a05cfa9ea57158f694c614723c
ee26e9@.dbforumz.com...
>I have created a view for reporting. I'm basically just joining a few
> tables. It is for a University so the results shows students names and
> the credits they are currently taking and the school code (There is 3
> Colleges under one ownership)
> The problem is some students attend two colleges and appear twice,
> one for each enrollment. For example
> FName LName Credits SchoolCode
> John Smith 12 1468
> John Smith 4 1469
> I need to combine these results so it would look like this
> John Smith 16 1468
> This is not for all students just certain ones. I would like to do
> this in the view if possible. Any help is appreciated.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Programming...pict262850.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=904750|||Take a look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML