Stack Exchange
Selecting single column multiple times based on different conditions
I have written a SQL query to retrieve required data and it looks like given below:
SELECT  distinct p.person_id,p.birth_date,p.gender_code,
        wm_concat(distinct r.race_code) as race_code,p.hispanic_latino_code,
        c.clinically_diagnosed_code,
        wm_concat(distinct c.characteristic_code) as chara_codes,
        p.prev_adopted_code,p.age_adopted,
        FIRST_VALUE(pe.removed_date) OVER (ORDER BY pe.removed_date),
        count(pe.removed_date) as removal_count,
        LAST_VALUE(pe.discharge_date) OVER (ORDER BY pe.discharge_date),
        LAST_VALUE(pe.removed_date) OVER (ORDER BY  pe.removed_date) as latest_removal_date,pe.created_date,
        pe.removal_circumstance_code,wm_concat(distinct rr.removal_reason_code) as removal_reasons,
        ps.placement_type_code,ps.icpc_placement_flag,pe.caretaker_structure_code    
FROM PERSON p left outer join RACE r on p.person_id = r.person_id
       left outer join CHARACTERISTIC c on c.person_id = p.person_id
       left outer join PLACEMENT_EPISODE pe on p.person_id = pe.child_id
       left outer join PLACEMENT_SETTING ps on p.person_id = ps.child_id
       left outer join REMOVAL_REASON rr on pe.placement_episode_id = rr.placement_episode_id
GROUP BY p.person_id,p.birth_date,p.gender_code,p.hispanic_latino_code,
       c.clinically_diagnosed_code,p.prev_adopted_code,p.age_adopted,pe.removed_date,
       pe.discharge_date,pe.removed_date,pe.created_date,pe.removal_circumstance_code,
       ps.placement_type_code,ps.icpc_placement_flag,pe.caretaker_structure_code
ORDER BY p.person_id

In the above mentioned query, I have already selected birth date for a person. Now again in select clause I want to select birth_date for persons with following condition:
condition 1: p.person_id  =  pe.primary_caretaker_id
condition 2: p.person_id  =  pe.secondary_caretaker_id  

Can someone tell me the way to select these fields(birth_date based on two different conditions) in the existing query?
Birth_date has been already selected once for individual person. Now I want to retrieve birth_date for primary_caretaker and secondary_caretaker.

You can just alias the table and join on the first table with the "individual" conditions and the 2nd table (alias) with the second set of caretaker conditions.

You will need to join to the PERSON table twice more:
SELECT  distinct p.person_id,p.birth_date,p.gender_code,
        wm_concat(distinct r.race_code) as race_code,p.hispanic_latino_code,
        c.clinically_diagnosed_code,
        wm_concat(distinct c.characteristic_code) as chara_codes,
        p.prev_adopted_code,p.age_adopted,
        FIRST_VALUE(pe.removed_date) OVER (ORDER BY pe.removed_date),
        count(pe.removed_date) as removal_count,
        LAST_VALUE(pe.discharge_date) OVER (ORDER BY pe.discharge_date),
        LAST_VALUE(pe.removed_date) OVER (ORDER BY  pe.removed_date) as latest_removal_date,
        pe.created_date,
        pe.removal_circumstance_code,wm_concat(distinct rr.removal_reason_code) as removal_reasons,
        ps.placement_type_code,ps.icpc_placement_flag,pe.caretaker_structure_code,
        primCare.birth_date as primary_carer_birth_date, 
        secCare.birth_date as secondary_carer_birth_date,
FROM PERSON p left outer join RACE r on p.person_id = r.person_id
       left outer join PERSON primCare on primCare.person_id = pe.primary_caretaker_id
       left outer join PERSON secCare on secCare.person_id = pe.secondary_caretaker_id
       left outer join CHARACTERISTIC c on c.person_id = p.person_id
       left outer join PLACEMENT_EPISODE pe on p.person_id = pe.child_id
       left outer join PLACEMENT_SETTING ps on p.person_id = ps.child_id
       left outer join REMOVAL_REASON rr on pe.placement_episode_id = rr.placement_episode_id
GROUP BY p.person_id,p.birth_date,p.gender_code,p.hispanic_latino_code,
       c.clinically_diagnosed_code,p.prev_adopted_code,p.age_adopted,pe.removed_date,
       pe.discharge_date,pe.removed_date,pe.created_date,pe.removal_circumstance_code,
       ps.placement_type_code,ps.icpc_placement_flag,pe.caretaker_structure_code, primCare.birth_date, secCare.birth_date
ORDER BY p.person_id
