Recursive and Transpose Result Set

Database

Recursive and Transpose Result Set

Hi,

I am pretty new to Recursive and have a requirement as below and not sure as to how to go about solving the same.

Below is a sort of scenario. 

CREATE VOLATILE TABLE Accounts12
 (
 Acct_Id INTEGER
 )
 PRIMARY INDEX (Acct_Id)
 ON COMMIT PRESERVE ROWS;
 
 INsert Into Accounts12 VALUES (11);
 
 CREATE VOLATILE TABLE Accounts12_Hierarchy
 (
 Acct_Id INTEGER,
 Acct_Prnt_Id INTEGER
 )
 PRIMARY INDEX (Acct_Id,Acct_Prnt_Id)
 ON COMMIT PRESERVE ROWS;
 
 INsert Into Accounts12_Hierarchy VALUES (11,22);
 INsert Into Accounts12_Hierarchy VALUES (22,44);
 INsert Into Accounts12_Hierarchy VALUES (99,33);
 INsert Into Accounts12_Hierarchy VALUES (44,99);
 
 CREATE VOLATILE TABLE Accounts12_Description
 (Acct_Id INTEGER,
 Acct_Descrptn Varchar(50)
 )
 PRIMARY INDEX (Acct_Id)
 ON COMMIT PRESERVE ROWS;
 
 Insert Into Accounts12_Description Values (11,'Account11');
 Insert Into Accounts12_Description Values (22,'Account22');
 Insert Into Accounts12_Description Values (33,'Account33');
 Insert Into Accounts12_Description Values (44,'Account44');
 Insert Into Accounts12_Description Values (99,'Account99');

I would like to see the Ouput as below

AcctId AcctId_Lvl1  AcctId_Des1  AcctId_Lvl2 AcctId_Des2  AcctId_Lvl4   AcctId_Des3    AcctId_Lvl4     AcctId_Des4      AcctId_Lvl5     AcctId_Des5

------ --------------  -----------     -----------    -----------      -----------     -----------        -----------       -----------          -----------        -----------

11     22                Account22     44               Account44     99               Account99        33                 Account33          ?                    ?

This would not be dynamic. The query has to retreive only 5 levels of information

I believe a Recursive has to be used for this, but i also need the records of the recursive to be transposed.

Thanks