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...
TakamasaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Habib PourfardSoftware DeveloperCommented:
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

0
TakamasaAuthor Commented:
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...
0
Habib PourfardSoftware DeveloperCommented:
You could create a View and use it as a source for different reports, I don't have any other idea.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

awking00Commented:
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?
0
TakamasaAuthor Commented:
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
0
TakamasaAuthor Commented:
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

0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can try the following code which will meet your requirements

SELECT TB.Bank,TB.TranType,TB.Banks VALUE FROM 
(
select *
from Bank a
unpivot
(
  Banks
  for BankRulecol in ([Col1],[Col2],[Col3])
) unpiv
) TB
INNER JOIN BankRule B ON TB.Bank = B.Bank AND TB.TranType = B.TranType AND TB.BankRulecol = LTRIM(B.Field)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
What dbms (version and release) are you using (e.g. Oracle 11gR2)?
0
TakamasaAuthor Commented:
Hello Vikas

This is great. Thank you for your help !
0
TakamasaAuthor Commented:
Hi awking00

I am using SQL Server 2008 R2.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.