[cfExplode]

I have exhausted google and dbforums looking for examples on how to combine multiple returned rows to one single row. Here's my query.

SELECT 
PROD9.EMPLOYEE.EMPLOYEE, 
PROD9.EMPLOYEE.LAST_NAME, 
PROD9.EMPLOYEE.FIRST_NAME,   
PROD9.PAEMPLOYEE.WK_PHONE_NBR, 
PROD9.PAEMPLOYEE.WK_PHONE_EXT, 
prod9.deptcode.R_NAME,  
                
    CASE 
           WHEN PROD9.HRCONTNBR.CONTACT_TYPE = 'RADIO' 
              then PROD9.HRCONTNBR.PHONE END AS RADIONUM,
    CASE 
            WHEN PROD9.HRCONTNBR.CONTACT_TYPE = 'CELL' 
              then PROD9.HRCONTNBR.PHONE  END AS CELLNUM                 
FROM PROD9.EMPLOYEE
LEFT OUTER JOIN PROD9.HRCONTNBR ON PROD9.HRCONTNBR.ID_NBR 
          = PROD9.EMPLOYEE.EMPLOYEE
LEFT OUTER JOIN PROD9.PAEMPLOYEE ON PROD9.PAEMPLOYEE.EMPLOYEE 
          = PROD9.EMPLOYEE.EMPLOYEE
LEFT OUTER JOIN PROD9.DEPTCODE ON PROD9.DEPTCODE.DEPARTMENT 
          = PROD9.EMPLOYEE.DEPARTMENT
WHERE (PROD9.EMPLOYEE.EMPLOYEE = 1009) AND 
          (PROD9.EMPLOYEE.EMP_STATUS LIKE '%A%')  

This query returns the following
EMPLOYEE | LAST_NAME | FIRST_NAME | .... | RADIONUM | CELLNUM
1009 | SMITH | JOHN | .... | 29 | NULL
1009 | SMITH | JOHN | .... | NULL | 999-999-9999

What I'm trying to accomplish is this
EMPLOYEE | LAST_NAME | FIRST_NAME | .... | RADIONUM | CELLNUM
1009 | SMITH | JOHN | .... | 29 | 999-999-9999

I've looked at examples, but none quite like what I'm looking for. Please help?!

[chuck_forbes]

SELECT 
PROD9.EMPLOYEE.EMPLOYEE, 
PROD9.EMPLOYEE.LAST_NAME, 
PROD9.EMPLOYEE.FIRST_NAME,   
PROD9.PAEMPLOYEE.WK_PHONE_NBR, 
PROD9.PAEMPLOYEE.WK_PHONE_EXT, 
prod9.deptcode.R_NAME,  
                
MAX(    CASE 
           WHEN PROD9.HRCONTNBR.CONTACT_TYPE = 'RADIO' 
              then PROD9.HRCONTNBR.PHONE END) AS RADIONUM,
MAX(    CASE 
            WHEN PROD9.HRCONTNBR.CONTACT_TYPE = 'CELL' 
              then PROD9.HRCONTNBR.PHONE  END) AS CELLNUM                 
FROM PROD9.EMPLOYEE
LEFT OUTER JOIN PROD9.HRCONTNBR ON PROD9.HRCONTNBR.ID_NBR 
          = PROD9.EMPLOYEE.EMPLOYEE
LEFT OUTER JOIN PROD9.PAEMPLOYEE ON PROD9.PAEMPLOYEE.EMPLOYEE 
          = PROD9.EMPLOYEE.EMPLOYEE
LEFT OUTER JOIN PROD9.DEPTCODE ON PROD9.DEPTCODE.DEPARTMENT 
          = PROD9.EMPLOYEE.DEPARTMENT
WHERE (PROD9.EMPLOYEE.EMPLOYEE = 1009) AND 
          (PROD9.EMPLOYEE.EMP_STATUS LIKE '%A%')  
GROUP BY PROD9.EMPLOYEE.EMPLOYEE, 
PROD9.EMPLOYEE.LAST_NAME, 
PROD9.EMPLOYEE.FIRST_NAME,   
PROD9.PAEMPLOYEE.WK_PHONE_NBR, 
PROD9.PAEMPLOYEE.WK_PHONE_EXT, 
prod9.deptcode.R_NAME

[chuck_forbes]

or

SELECT 
PROD9.EMPLOYEE.EMPLOYEE, 
PROD9.EMPLOYEE.LAST_NAME, 
PROD9.EMPLOYEE.FIRST_NAME,   
PROD9.PAEMPLOYEE.WK_PHONE_NBR, 
PROD9.PAEMPLOYEE.WK_PHONE_EXT, 
prod9.deptcode.R_NAME,  
T1.PHONE AS RADIONUM,
T2.PHONE AS CELLNUM                 
FROM PROD9.EMPLOYEE
LEFT OUTER JOIN PROD9.HRCONTNBR T1 ON T1.ID_NBR 
          = PROD9.EMPLOYEE.EMPLOYEE AND
             T1.CONTACT_TYPE = 'RADIO' 
LEFT OUTER JOIN PROD9.HRCONTNBR T2 ON T2.ID_NBR 
          = PROD9.EMPLOYEE.EMPLOYEE AND
             T2.CONTACT_TYPE = 'CELL' 
LEFT OUTER JOIN PROD9.PAEMPLOYEE ON PROD9.PAEMPLOYEE.EMPLOYEE 
          = PROD9.EMPLOYEE.EMPLOYEE
LEFT OUTER JOIN PROD9.DEPTCODE ON PROD9.DEPTCODE.DEPARTMENT 
          = PROD9.EMPLOYEE.DEPARTMENT
WHERE (PROD9.EMPLOYEE.EMPLOYEE = 1009) AND 
          (PROD9.EMPLOYEE.EMP_STATUS LIKE '%A%') 

[cfExplode]

very cool solution, chuck. thank you so very much! I ended using your second solution.

thanks again!!