title: How to output data on one line per ID in Oracle SQL?
body: <p>This works to get me all the data I need, but outputs a separate row for each side of the UNION when what I really want is to get all parent contacts on the same row as the student ID. That is, I want a row with <strong>a.id</strong> and then all the info for <strong>contact_1, contact_2, contact_3 and contact_4</strong> in the same row. I'm guessing UNION was the wrong choice...</p>
  <pre><code>SELECT final.* 
      FROM (
          SELECT 
          --Name 1 in P1 household
          a.id
          ,a.name1_web_user_id as contact_1_id
          ,a.name1_full_name as contact_1_name
          ,case 'Name1:Mobile' 
              when Other_No_Type_1 then Other_No_1
              when Other_No_Type_2 then Other_No_2
              when Other_No_Type_3 then Other_No_3
              when Other_No_Type_4 then Other_No_4
              end as contact_1_mobile
          ,a.email as contact_1_email
         
          --Name 2 in P1 household
          ,a.name2_web_user_id as contact_2_id
          ,a.name2_full_name as contact_2_name
          ,case 'Name2:Mobile' 
              when Other_No_Type_1 then Other_No_1
              when Other_No_Type_2 then Other_No_2
              when Other_No_Type_3 then Other_No_3
              when Other_No_Type_4 then Other_No_4
              end as contact_2_mobile
          ,a.EMAIL_2 as contact_2_email
              
      FROM rg_student s left outer join rg_addr a on s.id = a.id
                  WHERE  (
                  (a.addr_code='P1' AND a.rg_active = 'Y') AND ((a.name1_web_user_id is not null) OR (a.name2_web_user_id is not null))
                              AND a.id in(SELECT id from rg_student where student_group='Student')
                          )
      UNION
      
          SELECT 
          --Name 1 in P2 household
          a.id
          ,a.name1_web_user_id as contact_3_id
          ,a.name1_full_name as contact_3_name
          ,case 'Name1:Mobile' 
              when Other_No_Type_1 then Other_No_1
              when Other_No_Type_2 then Other_No_2
              when Other_No_Type_3 then Other_No_3
              when Other_No_Type_4 then Other_No_4
              end as contact_3_mobile
          ,a.email as contact_3_email
          
          --Name 2 in P2 household
          ,a.name2_web_user_id as contact_4_id
          ,a.name2_full_name as contact_4_name
          ,case 'Name2:Mobile' 
              when Other_No_Type_1 then Other_No_1
              when Other_No_Type_2 then Other_No_2
              when Other_No_Type_3 then Other_No_3
              when Other_No_Type_4 then Other_No_4
              end as contact_4_mobile
          ,a.EMAIL_2 as contact_4_email
              
      FROM rg_student s left outer join rg_addr a on s.id = a.id
                  WHERE  (
                          (a.addr_code='P2' AND a.rg_active = 'Y') AND ((a.name1_web_user_id is not null) OR (a.name2_web_user_id is not null))
                              AND a.id in(SELECT id from rg_student where student_group='Student')
                          )                
          )final
      ORDER BY final.id
  </code></pre>
  <p>I've tried to replace the UNION with JOIN, as guided, and simplified the select as follows, but now get error about FROM being in the wrong place.</p>
  <pre><code>
          SELECT 
          --Name 1 in P1 household
          s.id
          ,a.name1_web_user_id as contact_1_id
          ,a.name1_full_name as contact_1_name
          ,case 'Name1:Mobile' 
              when a.Other_No_Type_1 then Other_No_1
              when a.Other_No_Type_2 then Other_No_2
              when a.Other_No_Type_3 then Other_No_3
              when a.Other_No_Type_4 then Other_No_4
              end as contact_1_mobile
          ,a.email as contact_1_email
         
          --Name 2 in P1 household
          ,a.name2_web_user_id as contact_2_id
          ,a.name2_full_name as contact_2_name
          ,case 'Name2:Mobile' 
              when a.Other_No_Type_1 then Other_No_1
              when a.Other_No_Type_2 then Other_No_2
              when a.Other_No_Type_3 then Other_No_3
              when a.Other_No_Type_4 then Other_No_4
              end as contact_2_mobile
          ,a.email_2 as contact_2_email
              
          --Name 1 in P2 household
          s.id
          ,b.name1_web_user_id as contact_3_id
          ,b.name1_full_name as contact_3_name
          ,case 'Name1:Mobile' 
              when b.Other_No_Type_1 then Other_No_1
              when b.Other_No_Type_2 then Other_No_2
              when b.Other_No_Type_3 then Other_No_3
              when b.Other_No_Type_4 then Other_No_4
              end as contact_3_mobile
          ,b.email as contact_3_email
          
          --Name 2 in P2 household
          ,b.name2_web_user_id as contact_4_id
          ,b.name2_full_name as contact_4_name
          ,case 'Name2:Mobile' 
              when b.Other_No_Type_1 then Other_No_1
              when b.Other_No_Type_2 then Other_No_2
              when b.Other_No_Type_3 then Other_No_3
              when b.Other_No_Type_4 then Other_No_4
              end as contact_4_mobile
          ,b.email_2 as contact_4_email
              
      FROM rg_student s
          left outer join rg_addr a on s.id=a.id
          WHERE  ((a.addr_code='P1' AND a.rg_active = 'Y') 
                  AND s.id in(SELECT id from rg_student where student_group='Student'))
          left outer join rg_addr b on s.id=b.id
          WHERE  ((a.addr_code='P2' AND b.rg_active = 'Y') 
                   AND s.id in(SELECT id from rg_student where student_group='Student'))
  </code></pre>
  <p>This now gives me the single row per student that I need, but also disregards my need to (SELECT id from rg_student where student_group='Student') in the FROM, so it's now returning all students in any group (incl. withdrawn, graduate, etc.). I hear that I can't use WHERE in the FROM, but not sure how to define the student_group I need.</p>
  <pre><code>       SELECT DISTINCT
         --Name 1 in P1 household
         s.id
         ,a.name1_web_user_id as contact_1_id
         ,a.name1_full_name as contact_1_name
         ,case 'Name1:Mobile' 
             when a.Other_No_Type_1 then a.Other_No_1
             when a.Other_No_Type_2 then a.Other_No_2
             when a.Other_No_Type_3 then a.Other_No_3
             when a.Other_No_Type_4 then a.Other_No_4
             end as contact_1_mobile
         ,a.email as contact_1_email
        
         --Name 2 in P1 household
         ,a.name2_web_user_id as contact_2_id
         ,a.name2_full_name as contact_2_name
         ,case 'Name2:Mobile' 
             when a.Other_No_Type_1 then a.Other_No_1
             when a.Other_No_Type_2 then a.Other_No_2
             when a.Other_No_Type_3 then a.Other_No_3
             when a.Other_No_Type_4 then a.Other_No_4
             end as contact_2_mobile
         ,a.email_2 as contact_2_email
             
         --Name 1 in P2 household
         ,s.id
         ,b.name1_web_user_id as contact_3_id
         ,b.name1_full_name as contact_3_name
         ,case 'Name1:Mobile' 
             when b.Other_No_Type_1 then b.Other_No_1
             when b.Other_No_Type_2 then b.Other_No_2
             when b.Other_No_Type_3 then b.Other_No_3
             when b.Other_No_Type_4 then b.Other_No_4
             end as contact_3_mobile
         ,b.email as contact_3_email
         
         --Name 2 in P2 household
         ,b.name2_web_user_id as contact_4_id
         ,b.name2_full_name as contact_4_name
         ,case 'Name2:Mobile' 
             when b.Other_No_Type_1 then b.Other_No_1
             when b.Other_No_Type_2 then b.Other_No_2
             when b.Other_No_Type_3 then b.Other_No_3
             when b.Other_No_Type_4 then b.Other_No_4
             end as contact_4_mobile
         ,b.email_2 as contact_4_email
             
     FROM rg_student s
         left outer join rg_addr a on s.id=a.id
         AND  a.addr_code='P1' AND a.rg_active = 'Y' 
         AND s.id in(SELECT id from rg_student where student_group='Student')
             left outer join rg_addr b on s.id=b.id
             AND  a.addr_code='P2' AND b.rg_active = 'Y' 
             AND s.id in(SELECT id from rg_student where student_group='Student')
  </code></pre>