merging multiple rows into 1 - SQL Server Forums

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)

merging multiple rows into 1

Author

Topic

bradm77
Starting Member

6 Posts

Posted - 2008-01-16 : 10:49:58

I have a sql 2000 DB that contains account info for an internal banking-type system (written in asp.net). Each customer has 4 different accounts. when I need to get account balances, I run the following to get the contents of the endbal column with the most recent date for each account:

SELECT clients.idnum, clients.lname + ',' + clients.fname as clientname, rh.accountid as ACCOUNT, rh.endbal as BALANCE
FROM transactions as rh
join clients on clients.idnum=rh.clientid,(SELECT max(cast([date] as datetime)) as maxdate,
clientid, accountid
FROM transactions
GROUP BY clientid,accountid) as maxresults
WHERE rh.clientid = maxresults.clientid AND cast(rh.[date] as datetime)= maxresults.maxdate
AND rh.accountid=maxresults.accountid
order by clientname
Here is an example of what I get returned for each customer:

ID NAME ACCT BALANCE
867539 SMITH,JOHN ACCT A .1600
867539 SMITH,JOHN ACCT B 861.5600
867539 SMITH,JOHN ACCT C 997.0800
867539 SMITH,JOHN ACCT D 50.0000

This is fine for running a client balance report, but what is needed now is a report that shows the following 7 columns with just 1 row per customer:

ID NAME ACCT A ACCT B ACCT C ACCT D TOTAL OF ALL ACCTS

I am at a complete loss of how to do this in SQL.

Any help/suggestions greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 11:04:29

use this query to get desired result:-

SELECT clients.idnum, clients.lname + ',' + clients.fname as clientname, 
SUM(CASE WHEN rh.accountid ='ACCT A' THEN rh.endbal ELSE 0 END) as [ACCT A], 
SUM(CASE WHEN rh.accountid ='ACCT B' THEN rh.endbal ELSE 0 END) as [ACCT B],
SUM(CASE WHEN rh.accountid ='ACCT C' THEN rh.endbal ELSE 0 END) as [ACCT C],
SUM(CASE WHEN rh.accountid ='ACCT D' THEN rh.endbal ELSE 0 END) as [ACCT D],
SUM(rh.endbal) as [TOTAL OF ALL ACCTS]
FROM transactions as rh
join clients on clients.idnum=rh.clientid,(SELECT max(cast([date] as datetime)) as maxdate,
clientid, accountid
FROM transactions
GROUP BY clientid,accountid) as maxresults
WHERE rh.clientid = maxresults.clientid AND cast(rh.[date] as datetime)= maxresults.maxdate
AND rh.accountid=maxresults.accountid
GROUP BY clients.idnum, clients.lname + ',' + clients.fname
order by clientname

bradm77
Starting Member

6 Posts

Posted - 2008-01-16 : 11:22:06

That worked great, thanks!!! One small glitch though...... for the purposes of the display and total on this report only, they want the balance of 'acct a' to ALWAYS be 200.00, irregardless of what is really is, and they want the balance of 'acct d' to ALWAYS be 50.00, no matter what it really is.

Is that even possible?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-16 : 11:25:56

[code]
SELECT clients.idnum, clients.lname + ',' + clients.fname as clientname,
200.00 as [ACCT A],
SUM(CASE WHEN rh.accountid ='ACCT B' THEN rh.endbal ELSE 0 END) as [ACCT B],
SUM(CASE WHEN rh.accountid ='ACCT C' THEN rh.endbal ELSE 0 END) as [ACCT C],
50.00 as [ACCT D],
SUM(rh.endbal) as [TOTAL OF ALL ACCTS]
FROM transactions as rh
join clients on clients.idnum=rh.clientid,(SELECT max(cast([date] as datetime)) as maxdate,
clientid, accountid
FROM transactions
GROUP BY clientid,accountid) as maxresults
WHERE rh.clientid = maxresults.clientid AND cast(rh.[date] as datetime)= maxresults.maxdate
AND rh.accountid=maxresults.accountid
GROUP BY clients.idnum, clients.lname + ',' + clients.fname
order by clientname
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

bradm77
Starting Member

6 Posts

Posted - 2008-01-16 : 11:36:53

Ok, but the total I get is still the actual total of endbal for the 4 accounts. What I need to show as the total is:

acctb + acctc + 200 + 50.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 11:51:01

quote:
Originally posted by bradm77

Ok, but the total I get is still the actual total of endbal for the 4 accounts. What I need to show as the total is:

acctb + acctc + 200 + 50.

SELECT clients.idnum, clients.lname + ',' + clients.fname as clientname, 
200.00 as [ACCT A], 
SUM(CASE WHEN rh.accountid ='ACCT B' THEN rh.endbal ELSE 0 END) as [ACCT B],
SUM(CASE WHEN rh.accountid ='ACCT C' THEN rh.endbal ELSE 0 END) as [ACCT C],
50.00 as [ACCT D],
SUM(CASE WHEN rh.accountid ='ACCT A' THEN 200 WHEN rh.accountid ='ACCT D' THEN 50 ELSE rh.endbal END) as [TOTAL OF ALL ACCTS]
FROM transactions as rh
join clients on clients.idnum=rh.clientid,(SELECT max(cast([date] as datetime)) as maxdate,
clientid, accountid
FROM transactions
GROUP BY clientid,accountid) as maxresults
WHERE rh.clientid = maxresults.clientid AND cast(rh.[date] as datetime)= maxresults.maxdate
AND rh.accountid=maxresults.accountid
GROUP BY clients.idnum, clients.lname + ',' + clients.fname
order by clientname

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-16 : 11:53:11


SELECT clients.idnum, clients.lname + ',' + clients.fname as clientname, 
200.00 as [ACCT A], 
SUM(CASE WHEN rh.accountid ='ACCT B' THEN rh.endbal ELSE 0 END) as [ACCT B],
SUM(CASE WHEN rh.accountid ='ACCT C' THEN rh.endbal ELSE 0 END) as [ACCT C],
50.00 as [ACCT D],
SUM
(
 CASE WHEN rh.accountid ='ACCT A' THEN 200 ELSE 0 END + 
 CASE WHEN rh.accountid ='ACCT B' THEN rh.endbal ELSE 0 END + 
 CASE WHEN rh.accountid ='ACCT C' THEN rh.endbal ELSE 0 END +
 CASE WHEN rh.accountid ='ACCT D' THEN 50 ELSE 0 END + 
) as [TOTAL OF ALL ACCTS]
FROM transactions as rh
join clients on clients.idnum=rh.clientid,(SELECT max(cast([date] as datetime)) as maxdate,
clientid, accountid
FROM transactions
GROUP BY clientid,accountid) as maxresults
WHERE rh.clientid = maxresults.clientid AND cast(rh.[date] as datetime)= maxresults.maxdate
AND rh.accountid=maxresults.accountid
GROUP BY clients.idnum, clients.lname + ',' + clients.fname
order by clientname

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

bradm77
Starting Member

6 Posts

Posted - 2008-01-16 : 13:17:08

That returns an error -

Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'as'.

bradm77
Starting Member

6 Posts

Posted - 2008-01-16 : 13:19:27

never mind, got rid of that final + and it works perfect now - Thanks!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources