Link to home
Start Free TrialLog in
Avatar of Takamasa
TakamasaFlag for Japan

asked on

SQL query question

I need to write a SQL query on SQL Server 2008 to make a summary report, which consists of [Company, Type, Amount].  
but there are multiple columns for amount, and depending on Company and Type, I need to select different columns to get the amount.

For a simplified example, my source data looks like this:

Data(Bank, TranType, Col1, Col2, Col3)

'ANZ', 'A', 1000, 1005, 980
'ANZ', 'B', 500, 502, 496
'ANZ', 'C', 1000, 1000, 1000
'HSBC', 'A', 1000, 1005, 980
'HSBC', 'B', 500, 502, 496
'CITI', 'C', 1000, 1000, 1000


And I have a table like this for rules:

Rule(Bank, TranType, Field)

'ANZ', 'A', 'Col1'
'ANZ', 'B', 'Col2'
'ANZ', 'C', 'Col3'
'HSBC', 'A', 'Col2'
'HSBC', 'B', 'Col3'
'CITI', 'A', 'Col1'

Any simple query to solve this ? Experts, your help is really appreciated...
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

You can use Case When like the following code:

SELECT Bank, TranType,
SUM (
	CASE WHEN Bank = 'ANZ' THEN Col1 
	WHEN Bank = 'BNZ' OR Bank = 'HSBC' Then Col2
	ELSE 0 END
	)
FROM data
GROUP BY Bank, TranType

Open in new window

Avatar of Takamasa

ASKER

Hi Habib,
Thank you for your suggestion.

The rule table actually is a very long list and it can change quite often, so I do not want to hard-code the rule...
You could create a View and use it as a source for different reports, I don't have any other idea.
You show trantype of 'C' for bank CITI in data and trantype of 'A' for bank CITI in rule. Is that a typo? Also, can you post what your desired output should be given this sample data?
Hi awking00

Thank you for your reply.
If the combinaton of Bank-TranType do not match, then the report will not pick up.
The output would look like this:

ANZ, A, 1000
ANZ, B, 502
ANZ, C, 1000
HSBC, A, 1005
HSBC, B, 496
Hi Habib,

Thank you for your suggestion.

Below query returns the output I am looking for, but in my real case the CASE statement would get very long (40+ when clauses). I was hoping there might be a smarter way than that but maybe some hard-coding cannot be avoided...

select 
	a.bank, 
	a.trantype, 
	case 
		when b.colname = 'col1' then a.col1
		when b.colname = 'col2' then a.col2
		when b.colname = 'col3' then a.col3
	end as amount
from 
	data a
	inner join 
	rule b
on 
	a.bank = b.bank 
	and a.trantype = b.trantype

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What dbms (version and release) are you using (e.g. Oracle 11gR2)?
Hello Vikas

This is great. Thank you for your help !
Hi awking00

I am using SQL Server 2008 R2.