Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: Need a little help with combing rows

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

    Code:
    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?!
    Last edited by cfExplode; 10-09-08 at 16:53.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    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

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    or

    Code:
    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%')

  4. #4
    Join Date
    Oct 2008
    Posts
    2
    very cool solution, chuck. thank you so very much! I ended using your second solution.

    thanks again!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •