SQL Query

[joechan]

Hi all,

I got 2 tables : Account_Header and Account_Transaction
with the following fields :
Account_Header : ACCT_CODE, ACCT_NAME
Account_Transaction : ACCT_CODE, TXN_AMT

I want to obtain the a view of the each account with top 3 transactions like this:

ACCT_CODE TXN1 TXN2 TXN3
001 100 50 30
002 200 120 10
003 12 10
004
(in this case 001 and 002 got > 3 txns, 003 got 2 txns and 004 got no txn at all)

Any idea for this query in a more flexible way when the top X (say from top 3 to top 5) account changes? Currently I self-joined the transaction table 3 times to get the result.

Thanks
Joe

 

[amrita418]

Getting the top 3 transactions for a given Acct_Code is straight forward (As you might know already).

select 		a.Acct_Code, 	
		a.TXN_Amt 
from 		#AcctTrans a
where 		a.Txn_Amt in (select 	top 3 
					Txn_Amt 
			      from 	#AcctTrans b
                    	      where 	a.Acct_Code = B.Acct_Code
		    	      order by 	Txn_Amt desc)
order by 	Acct_Code, Txn_Amt desc

The tricky part is the transpose this data.

Sql Server (so far, I heard 2005 has them) does not have row_number or rank functions to order there fields like
Acct_Code Txn Rank
001 100 1
001 50 2
001 30 3
002 200 1
002 120 2 and so on...

Hence, you need to do this in two steps:

1) Read each row and load the values into a temp table with a ranking field.
2) Use the case with group by clause to transpose the data.

Sorry that I could not provide a better solution.

Regards,
AA