How to get same columns with alias based on another column

Question

Hi  All,


I need to get  part_id as first column next six columns based on process_id=21 and next six columns based on process_id=42 and so on..it retrieves duplicate records.Please help

     SELECT   DISTINCT A.PART_ID,
                  A.resources,
                  A.SCRAP_PCT,
                  A.FPLT,
                  A.ATP_FAMILY,
                  A.rfgo_family,
                  A.gfgo_family,
                  B.Sec_Resources,
                  B.SEC_SCRAP_PCT,
                  B.SEC_FPLT,
                  B.SEC_atp_family,
                  B.SEC_rfgo_family,
                  B.SEC_gfgo_family
  FROM       (SELECT   DISTINCT a.PART_ID,
                                  f.name Resources,
                                  a.SCRAP_PCT,
                                  a.FPLT,
                                  e.NAME atp_family,
                                  c.NAME rfgo_family,
                                  d.NAME gfgo_family
                  FROM   s_ftl_line_mfg_process_map a,
                         s_ft_part_params b,
                         s_ft_rfgo_family c,
                         s_ft_gfgo_family d,
                         s_ft_atp_family e,
                         S_FTL_MACHINE_RESOURCE f
                 WHERE       f.name IS NOT NULL
                         AND a.part_id = b.part_id
                         AND b.rfgo_family_id = c.rfgo_family_id(+)
                         AND b.gfgo_family_id = d.gfgo_family_id(+)
                         AND b.atp_family_id = e.atp_family_id(+)
                        -- AND a.part_id = f.part_id(+)
                         AND A.PROCESS_ID = '21') A
            JOIN
               (SELECT   DISTINCT a.PART_ID,
                                  f.name Sec_Resources,
                                  a.SCRAP_PCT SEC_SCRAP_PCT,
                                  a.FPLT SEC_FPLT,
                                  e.NAME SEC_atp_family,
                                  c.NAME SEC_rfgo_family,
                                  d.NAME SEC_gfgo_family
                  FROM   s_ftl_line_mfg_process_map a,
                         s_ft_part_params b,
                         s_ft_rfgo_family c,
                         s_ft_gfgo_family d,
                         s_ft_atp_family e,
                         S_FTL_MACHINE_RESOURCE f
                 WHERE       f.name IS NOT NULL
                         AND a.part_id = b.part_id(+)
                         AND b.rfgo_family_id = c.rfgo_family_id(+)
                         AND b.gfgo_family_id = d.gfgo_family_id(+)
                         AND b.atp_family_id = e.atp_family_id(+)
                       --  AND a.part_id = f.part_id(+)
                         AND A.PROCESS_ID = '42') B
            ON A.part_id = B.part_id

Thanks,
Crystal