Archives of the TeradataForum

Message Posted: Thu, 30 Jun 2005 @ 10:14:18 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, June 29, 2005 10:23 -->

Hello,

You can use the following generic method :

Suppose that the source table which contains the account info is - cust_acc. Lets create another table named - cust_acc_rank which has one additional column called 'rank_cust'.

Then get the rank on the cust_no and acc_no using the SQL :

     INSERT INTO cust_acc_rank
     SELECT a.cust_no,a.acc_no,a.bal,rank(a.cust_no,a.acc_no ASC )
     FROM cust_acc as a
     INNER JOIN cust_acc b
     ON a.cust_no = b.cust_no
     AND a.acc_no = b.acc_no

Then we can get all the relevant info of a customer by querying the cust_acc_rank table :

     SELECT a.cust_no,a.acc_no as acc_one,a.bal,b.acc_no as
     acc_two,b.bal,c.acc_no as acc_three,c.bal,d.acc_no as acc_four,d.bal
     FROM cust_acc_rank a
     INNER JOIN cust_acc_rank b
     ON a.cust_no    = b.cust_no
     AND a.rank_cust = b.rank_cust - 1
     INNER JOIN cust_acc_rank c
     ON a.cust_no    = c.cust_no
     AND a.rank_cust = c.rank_cust - 2
     INNER JOIN cust_acc_rank d
     ON a.cust_no    = d.cust_no
     AND a.rank_cust = d.rank_cust - 3

Hope this helps!

	<Prev	Next>		<<First	<Prev	Next>	Last>>