Multilevel collections - CRUD operations help

I am trying to build a collection which has 2 elements, The first element being the key and the second element having the concatenated string value of data for the same key.

Here is my Source Data Structure
CallNum EmplID

500      125
400      123
300      125
100      125
600      123

Desired Output Structure
EmpID Calls

125     500~300~100
123     400~600

I am trying to get this done using multilevel collections. The data in each row of the Source Data Structure shown above is in the form of variables
Here is the logic I am trying to accomplish using PLSQL

--> Create a multidimensional array (possibly varray)
--> For the emplid from the current row
--> Check if it exists

    If yes

       retrieve the Calls String Value
       Append to that value the CallNum value from current row
    else
       Add the EmplID
       Add the CallNum value to the Calls string value

I realize there could be alternative solutions to this , like tables etc, But constraints like data structure, data volume lead me to the multilevel collection solution.        

Any thoughts, Suggestions?

Thx
Alk Received on Thu Aug 17 2006 - 13:37:21 CDT