Need help with SELECT statement

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 2005 Forums

Transact-SQL (2005)

Need help with SELECT statement

Author

Topic

canaanp
Starting Member

1 Post

Posted - 2010-09-18 : 23:36:03

Hi, I'm working on a form for asp.net that has a gridview control. The select query that will populate this grid needs fields from two tables, Customers and Accounts.

This is what I have right now:


SELECT        Customers.CustomerLastName, Customers.CustomerFirstName, Accounts.AccountType, Accounts.AccountBalance
FROM            (Accounts INNER JOIN
                         Customers ON Accounts.CustomerID = Customers.CustomerID)

For accounttype there are only two possibilities, checking and savings. For each customer there are always one of each account. So the way this query is now will always return two records for each customer, one with account type checking and one with account type savings. I would like to make it so that there is one record for each customer with a column for checking balance, and a column for savings balance. Is this possible? Any help would be greatly appreciated. Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-09-19 : 00:17:37

You can use aggregate on case,like:

select ..., sum(case when Accounts.AccountType = 'Checking' then Accounts.AccountBalance else 0 end) as [CheckingBalance],
sum(case...)
from ...
group by ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-19 : 11:38:26


SELECT CustomerLastName,CustomerFirstName,[Checking] as [CheckingBalance],[Savings] as [SavingsBalance]
FROM (
SELECT        Customers.CustomerLastName, Customers.CustomerFirstName, Accounts.AccountType, Accounts.AccountBalance
FROM            Accounts 
INNER JOIN      Customers 
ON Accounts.CustomerID = Customers.CustomerID
)t
PIVOT (SUM(AccountBalance) FOR AccountType IN ([Checking],[Savings]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources