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

Combine SUMs

How can I combine the 2 Sum amounts below. Basically teh 2 queries are exactly the same, just hitting 2 different tables (pdc and pdcdeleted) with the same structure:

SELECT SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdc pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery

SELECT SUM(PQuery2.Amount) as PDCs_IL_deleted
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdcdeleted pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery2

Since there is no group by, I will assume that you just working with one value, then you can simply do something like:

declare @.PDCs_IL int
select @.PDCs_IL = SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,

declare @.PDCs_IL_deleted int
select @.PDCs_IL_deleted = SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,

select @.PDCs_IL + @.PDCs_IL_deleted

or

select ( first query ) + ( second query )

as in

create table #test
(
value int
)
insert into #test values (1)
insert into #test values (1)
insert into #test values (1)

select ( select sum(value) from #test ) + ( select sum(value) from #test )

--note that you might need to check for a NULL value :)

You could probably just add another left join to the deleted table and include the where clause, but it is really messy to contemplate all that you are trying to do without more information. Either of these other methods should work fine if the two queries already work fine.

sqlsql

Combine multiple rows into one

I have two tables that I need to join to get the required data, but I get too many rows returned with everything I have tried. Basically, both tables have and IDEA_ID field, and a simple SELECT query on TABLE1 will give me the data that I want from that table, but I get too many rows returned from TABLE2 as there are multiple rows with the same IDEA_ID in TABLE2.

Basically,

SELECT *
FROM TABLE1

gives me all the data I need with the exception of 6 columns for which the data is in TABLE2.

TABLE2 has 3 columns - IDEA_ID, ROLE_ID and ADATE. IDEA_ID matches the IDEA_ID field in TABLE1; ROLE_ID is a number from 1 to 6; and ADATE is a date. There can be 1 to 6 rows for each IDEA_ID, with the IDEA_ID and ROLE_ID combination being unique.

How can I run a query that will give me rows that have the fields from TABLE1, and 6 additional columns from TABLE2?

Table structures are:

TABLE1
IDEA_ID, PROD_TYPE, TITLE, DESC

TABLE2
IDEA_ID, ROLE_ID, ADATEWhich six columns do you need from the second table?

-PatP|||Pat

For each IDEA_ID in TABLE2, there are potentially 6 rows with that IDEA_ID, but with a number from 1 to 6 in the ROLE_ID column.

What I'm trying to do is to create a dataset that has all of the columns from TABLE1, but I want to create 6 new columns with data from TABLE2. Each of the new columns will represent a row from TABLE2 for each of the values of ROLE_ID from 1 to 6. The value that will go into each of these columns is the value in ADATE.

I can query TABLE2 to get these values:

SELECT idea_id,
decode(role_id, 1, adate),
decode(role_id, 2, adate),
decode(role_id, 3, adate),
decode(role_id, 4, adate),
decode(role_id, 5, adate),
decode(role_id, 6, adate)
FROM TABLE2

but this produces 6 rows of output. What I want from TABLE2 is a single row with each of these values. The query above produces a dataset with IDEA_ID plus 6 columns, each row having only one of those 6 column containing a value.

How do I produce just one row of output with all 6 of these columns having a value in it?

column with a number and 2 decimal like 10.10 or 150.30

Hello,

How should I create a column to save data with the folowing format 10.10 or 10.20 or 10.30 or 150.30 or 10 or 150.
It is basically process step in a diagram flow.
I tried with decimal but with 10.10 , it removes automatically the 0.

ThanksYou could set the precision to 2in the design view for the particular column. Alternatively, this kind of formatting is best done from your presentation layer. There are different varieties of DataFormatStrings available to preent the data in different ways.|||

Hi

You column type must be decimal(18, 0) .

change to decimal(18, 2) .

Column Width

How can you widen or shorten a column's width when the report is run?
The width doesn't allow custom code or expressions.
Basically, based on a parameter I pass in, I want to widen or shrink a
column's width.Anyone?
"JSF" wrote:
> How can you widen or shorten a column's width when the report is run?
> The width doesn't allow custom code or expressions.
> Basically, based on a parameter I pass in, I want to widen or shrink a
> column's width.|||JSF,
It doesn't look like it's possible to dynamically change column width.
I just did another search through the group and found nothing new.

Column Invisibility

Hey,

I am retrieving values from a database I have setup in sql server. Basically I want to make a column invisible if the value is null.

Here is the code I tried, but it didn't work.

=IIf(Fields!FundingCode.Valueisnothing,True, False)

I would greatly appreciate the help

a column of what? Datagrid, Gridview? What?