combining 2 rows

Hello,
I have a query that returns 2 rows, which I need to combine into 1 row.
The query looks like this:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
...the 2 rows that the query returns are:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 NULL 173.27
ClientA ClientA EUR 69 3465.29 NULL
...and I want to combine those 2 rows into a single row that looks like
this:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 3465.29 173.27
Thanks in advance,
Craig H.Use your query as a derived table and collapse the rows in a query around
it -- like this:
Select CName, AName, ACurrency, Payments, Sum( [Amount (incl. commission)]),
Sum ([Commission])
From
(
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
) T
Group By CName, AName, ACurrency, Payments
Order By CName, AName, ACurrency
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Craig H." <spam@.thehurley.com> wrote in message
news:u2%23N1$OnFHA.3336@.tk2msftngp13.phx.gbl...
> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.|||hi
it might work, if u remove
t.TCode from the group by clause
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Craig H." wrote:

> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.
>|||> it might work, if u remove
> t.TCode from the group by clause
No. It will give you the error t.TCCode is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:3485024E-D8DF-42DE-A9F5-13C37C0B22FF@.microsoft.com...
> hi
> it might work, if u remove
> t.TCode from the group by clause
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Craig H." wrote:
>|||Looks like you just need to remove TCode from your GROUP BY list:
...
GROUP BY t.CName, t.AName, t.ACurrency
David Portas
SQL Server MVP
--|||Good catch. I missed it too. So put the CASE expression inside the SUM
aggregate. Something like the following, depending on the desired
result of the SUM:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
SUM(CASE WHEN t.TCode = 'debit_batch'
THEN t.LAmount/t.ExchangeRate END) AS 'Amount (incl. commission)',
SUM(CASE WHEN t.TCode = 'commission'
THEN t.LAmount/t.ExchangeRate END) AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency
ORDER BY t.CName, t.AName, t.ACurrency ;
David Portas
SQL Server MVP
--|||David,
Correct me If I am wrong.
As per my understanding , removing TCode fro the Group By list will
throw the error
TCCode is invalid in the select list because it is not contained in
either an aggregate function or
the GROUP BY clause.
SELECT pub_id,
CASE WHEN type = 'business' THEN SUM(ytd_sales) END
FROM Titles
GROUP By pub_id
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
> Looks like you just need to remove TCode from your GROUP BY list:
> ...
> GROUP BY t.CName, t.AName, t.ACurrency
> --
> David Portas
> SQL Server MVP
> --
>|||Never mind. I had seen the other post only after sending this.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23iPyJWPnFHA.764@.TK2MSFTNGP14.phx.gbl...
> David,
> Correct me If I am wrong.
> As per my understanding , removing TCode fro the Group By list will
> throw the error
> TCCode is invalid in the select list because it is not contained in
> either an aggregate function or
> the GROUP BY clause.
>
> SELECT pub_id,
> CASE WHEN type = 'business' THEN SUM(ytd_sales) END
> FROM Titles
> GROUP By pub_id
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
>

Column summing other columns

Hi there
I've got this really basic question:
ocasionally I have to do something like this
select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
e.g. to sum up columns in another column.
it seems extremely unelegant, unfortunately using aliases in last
column ( SA+SB+SC) is impossible.
does it make sense performance-wise to leave such an operation to sql
server? will he optimize the query to prevent multiple calculation of
the same column sums, or should I rather sum the columns up on the
client side?
alos, isn't it possible to get rid of duplicating complicated
expressions by using some kind of alias if I want to use them in other
columns ?
thanks
Hp.
The following example is one way to simplify the calculation:
CREATE TABLE foo
(
a INT,
b INT,
c INT
)
INSERT foo SELECT 1, 1, 1
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 3, 4
INSERT foo SELECT 1, 3, 5
SELECT SA, SB, SC, SA + SB + SC
FROM (SELECT SUM(a) AS SA, SUM(b) AS SB, SUM(c) AS SC
FROMfoo) AS T1
HTH
- Peter Ward
WARDY IT Solutions
"H5N1" wrote:

> Hi there
> I've got this really basic question:
> ocasionally I have to do something like this
> select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
> e.g. to sum up columns in another column.
> it seems extremely unelegant, unfortunately using aliases in last
> column ( SA+SB+SC) is impossible.
>
> does it make sense performance-wise to leave such an operation to sql
> server? will he optimize the query to prevent multiple calculation of
> the same column sums, or should I rather sum the columns up on the
> client side?
> alos, isn't it possible to get rid of duplicating complicated
> expressions by using some kind of alias if I want to use them in other
> columns ?
> thanks
> Hp.
>

Column summing other columns

Hi there
I've got this really basic question:
ocasionally I have to do something like this
select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
e.g. to sum up columns in another column.
it seems extremely unelegant, unfortunately using aliases in last
column ( SA+SB+SC) is impossible.
does it make sense performance-wise to leave such an operation to sql
server? will he optimize the query to prevent multiple calculation of
the same column sums, or should I rather sum the columns up on the
client side?
alos, isn't it possible to get rid of duplicating complicated
expressions by using some kind of alias if I want to use them in other
columns ?
thanks
Hp.The following example is one way to simplify the calculation:
CREATE TABLE foo
(
a INT,
b INT,
c INT
)
INSERT foo SELECT 1, 1, 1
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 3, 4
INSERT foo SELECT 1, 3, 5
SELECT SA, SB, SC, SA + SB + SC
FROM (SELECT SUM(a) AS SA, SUM(b) AS SB, SUM(c) AS SC
FROM foo) AS T1
HTH
- Peter Ward
WARDY IT Solutions
"H5N1" wrote:

> Hi there
> I've got this really basic question:
> ocasionally I have to do something like this
> select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
> e.g. to sum up columns in another column.
> it seems extremely unelegant, unfortunately using aliases in last
> column ( SA+SB+SC) is impossible.
>
> does it make sense performance-wise to leave such an operation to sql
> server? will he optimize the query to prevent multiple calculation of
> the same column sums, or should I rather sum the columns up on the
> client side?
> alos, isn't it possible to get rid of duplicating complicated
> expressions by using some kind of alias if I want to use them in other
> columns ?
> thanks
> Hp.
>

column alias as variable

Is there a way to select a column as an alias using a variable for the alias? something like this:

SELECT Column1 as @.myVariable FROM Table1

Not directly in a SELECT statement. You can use dynamic SQL like:

set @.sql = N'SELECT Column1 as ' + quotename(@.myVariable) + N' FROM Table1'

exec sp_executesql @.sql

But it is not going to be pretty if you want to do this for multiple columns and dynamic SQL has security implications/management issues. Why do you want to do this? How will the client handle this if the column names can be modified arbitrarily? One way to do this is to fix the column names as c1, c2, c3 etc and have a separate result set or metadata that contains the user friendly names for c1, c2, c3 respectively.

|||

The user enters a period length such as 30 days and the report displays the period date ranges as the coulmn name of a pivot.

like this:

Location 1-30 31-60 61-90

Texas 10 3 2

Florida 5 8 7

Column alias

Can we have more than one column alias for a sinle column in the select
list.
Something like this
SELECT field1 A,B FROM Table
What for do you need it ? (BTW, columns can only have one Alias)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Gopinath S" <gopinath_s@.nospamtrigent.com> schrieb im Newsbeitrag
news:OAzset5WFHA.580@.TK2MSFTNGP15.phx.gbl...
> Can we have more than one column alias for a sinle column in the select
> list.
> Something like this
> SELECT field1 A,B FROM Table
|||Jens Smeyer wrote:
> What for do you need it ? (BTW, columns can only have one Alias)
>
Same stored procedure is being used by two different reports which
shoud have different aliases.
Thankx
Done a work around
|||You can use like this....
select ContactName as Name, ContactName as SecondName from Customers
thanks
"Gopinath S" <gopinath_s@.nospamtrigent.com> wrote in message
news:#nqI175WFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Jens Smeyer wrote:
> Same stored procedure is being used by two different reports which
> shoud have different aliases.
> Thankx
> Done a work around
|||Hi,
No need to have different alias. You can very well use the same alias for
both the reports.
Thanks
Hari
SQL Server MVP
"Gopinath S" <gopinath_s@.nospamtrigent.com> wrote in message
news:%23nqI175WFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Jens Smeyer wrote:
> Same stored procedure is being used by two different reports which shoud
> have different aliases.
> Thankx
> Done a work around