Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: SQL to flattening a table
 
From:   Anomy Anom

<-- 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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023