The data here is clients in programs. Most clients have more than one program. I would like to know if the results can be in one row. For example:
| first_name | last_name | dob | description | description | race | ethnicity_description | program_name |
|---|---|---|---|---|---|---|---|
| KERTA | SMITS | 1994-05-03 00:00:00.000 | New York | QUEENS | Black or African American | Non Hispanic or Latino | OUTPATIENT THERAPY |
| KERTA | SMITS | 1994-05-03 00:00:00.000 | New York | QUEENS | Black or African American | Non Hispanic or Latino | ARTICLE 46 |
All that is multiple is the last column (OUTPATIENT THERAPY and ARTICLE 46 program names. I would rather have the program_name of the second row should go on the first row result: Note many will have 3 program_name.
Query:
select ac.first_name, ac.last_name, ac.dob, st.description, co.description,
rd.race, rd.ethnicity_description, pe.program_name from address ad
inner join all_clients_view ac
on ad.people_id = ac.people_id
inner join county co
on ad.county = co.county_id
inner join state st
on co.state_id = st.state_id
inner join rpt_demographics rd
on ac.people_id = rd.people_id
right join program_enrollment_expanded_view pe
on pe.people_id = ac.people_id
where ad.is_active = 1
order by st.description, co.description