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

Column Comparison

Hi,
I work at a university and am dealing with government data. We
obtain a recordset from them called ISIRS. These are in a fixed length
field format that I put into a table. This table has upwards of 450
fields (there was no better way to break it up, no relations exist).
Each student can have multiple ISIR records, each with a transaction
number always incrementing. I was hoping someone could help me out
here...
I need to be able to compare the current ISIR with the students
previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
SQL Server Reports to present this data to the user and need a stored
procedure to do a column by column comparison and indicate the
differences in the row it returns.
psuedo-code:
returnRow = requested isir row
for each row in student's isirs
for each column in row's column
if returnRow[column] <> row[column] then
returnRow[column] = returnRow[column] + '#'
end if
end for each
end for each
I hope this makes sense. If we were using SQL Server 2005, I could do
this using CLR SP's, but we are on 2000.
Thanks for your help.Even with SQL Server 2005, I wouldn't reccomend doing this in a stored
procedure, becuase it would still involve hard coding the column names.
With 450 columns, it would make more sense to write a client side
application that iterates through each column programatically using the
column object reference number. The pseudo code you provided is similar to
the actual ADO.NET needed to do this.
http://support.microsoft.com/defaul...kb;en-us;310107
You said that each student can have multiple records, so you will need to
identify a primary key (ex: SSN + CourseNumber) to join each new records
with the appropriate new record.
"craiggwilson" <craiggwilson@.gmail.com> wrote in message
news:1140533890.693887.38640@.g14g2000cwa.googlegroups.com...
> Hi,
> I work at a university and am dealing with government data. We
> obtain a recordset from them called ISIRS. These are in a fixed length
> field format that I put into a table. This table has upwards of 450
> fields (there was no better way to break it up, no relations exist).
> Each student can have multiple ISIR records, each with a transaction
> number always incrementing. I was hoping someone could help me out
> here...
> I need to be able to compare the current ISIR with the students
> previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
> SQL Server Reports to present this data to the user and need a stored
> procedure to do a column by column comparison and indicate the
> differences in the row it returns.
> psuedo-code:
> returnRow = requested isir row
> for each row in student's isirs
> for each column in row's column
> if returnRow[column] <> row[column] then
> returnRow[column] = returnRow[column] + '#'
> end if
> end for each
> end for each
> I hope this makes sense. If we were using SQL Server 2005, I could do
> this using CLR SP's, but we are on 2000.
> Thanks for your help.
>|||That's what I was afraid of. The front end to this is an ASP.NET /
WindowsForms application, but the report is straight from a SQL Query.
I appreciate your help.|||I have had some success generating brute force SELECTs to do this sort
of thing by writing queries against the system tables (or
INFORMATION_SCHEMA views).
Start by writing a SELECT that tests one column of the table and
returns the key and both the current and prior values. What you need
is 450 such SELECTS with just the column names changed.
So then write a SELECT against syscolumns (or
INFORMATION_SCHEMA.COLUMNS) that creates one long string matching the
SELECT you just wrote, but concatenating in the column name in place
of the one you used in the example.
Obviously this is just a starting point, but I have used such
techniques successfully.
Good luck!
Roy
On 21 Feb 2006 06:58:10 -0800, "craiggwilson" <craiggwilson@.gmail.com>
wrote:

>Hi,
> I work at a university and am dealing with government data. We
>obtain a recordset from them called ISIRS. These are in a fixed length
>field format that I put into a table. This table has upwards of 450
>fields (there was no better way to break it up, no relations exist).
>Each student can have multiple ISIR records, each with a transaction
>number always incrementing. I was hoping someone could help me out
>here...
>I need to be able to compare the current ISIR with the students
>previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
>SQL Server Reports to present this data to the user and need a stored
>procedure to do a column by column comparison and indicate the
>differences in the row it returns.
>psuedo-code:
>returnRow = requested isir row
>for each row in student's isirs
> for each column in row's column
> if returnRow[column] <> row[column] then
> returnRow[column] = returnRow[column] + '#'
> end if
> end for each
>end for each
>I hope this makes sense. If we were using SQL Server 2005, I could do
>this using CLR SP's, but we are on 2000.
>Thanks for your help.