SQL select statement that would result in an associative table

Question

The company I work for uses an AS400 (iSeries). There is some data in a system dictionary that I am trying to pluck out and turn into an associative table. Here is what the data looks like

xtype | xdata    
 60   | 011111211            212  
 60   |                      345   
 60   | 212312               169  

xtype is the "key" that will allow me to return the relevant data.

212,345,169 are employee numbers and are in the left 3 characters of the 24 character xdata column.

011111211 is 3 "territories" (011, 111 and 211), likewise 212312 is 2 "territories" (212, 312)

What I would like to end up with is

empNum | territory    
------------------
   212 | 011      
   212 | 111      
   212 | 211        
   169 | 212    
   169 | 312

Here is what I have worked on so far:

 SELECT 
  *
 From
 (
   select 
   right(xdata,3) as empNum,
   trim(coalesce(left(xdata,3),'')) as ter
   from Table
   where xtype=60 and xarg < 960
 ) as outerTable
 where ter <> ''    

and

 trim(coalesce(substr(xdata,4,3),'')) as ter
 where ter <> ''

would work for the second territory

and

 trim(coalesce(substr(xdata,7,3),'')) as ter
 where ter <> ''

would work for the third territory

What I don't know is how to take those 3 and join them into a result that looks like an associative table. Any thoughts?

Answer 1

So you've got one query that returns 212 | 011 / 169 | 212, another that returns 212 | 111 / 169 | 312, and a third that returns 212 | 211, is that correct?

The obvious answer to transform this to the results you're asking for is to use UNION ALL to combine the three queries. You were looking at ways to join the queries, but (simply put) joining would add columns, when what you want to add is rows.