SQLTeam.com | Weblogs | Forums

Is it possible to combine records?

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

All programs in the same column or different columns?

if it can be a different.
like
Bob Jone Outpatient Article 46

What version (year) of SQL Server are you using?