This code is doing what I wanted with displaying colaterals when there are multiple but it is only showing rows which do have a colateral. I want to include also rows that have no colateral which we have here as cv.full_name. Is there a join problem?
select distinct ac.first_name, ac.last_name,cv.full_name as Colateral1, ac.id_no, ac.dob, st.description as state, co.description as county, datediff(yy, ac.dob, getdate()) as age,
ri.description as race, cp.ethnic1, pe.program_name, ih.income_amount,
row_number() over(partition by id_no, program_name, county, ri.description, ac.dob, ethnic1,ac.first_name, ac.last_name,
state
order by ih.income_amount) as rid
into #test
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
right join program_enrollment_expanded_view pe
on pe.people_id = ac.people_id
full outer join income_history_view ih
on ad.people_id = ih.people_id
inner join race ra
on ad.people_id = ra.people_id
inner join race_info ri
on ra.race_info_id = ri.race_info_id
inner join client_personal_view cp
on ad.people_id = cp.people_id
right join colaterals_view cv
on ad.people_id = cv.people_id
--where --ad.is_active = 1
--and ac.last_name = 'A Test'
--cv.event_name in('Personal Collateral', 'Family Collateral')
SELECT * into #pvt FROM
(
select
first_name, last_name, id_no, dob, state, county, age, race, ethnic1, program_name,[income_amount], rid,Colateral1
from #test
) t
PIVOT(
max(Colateral1)
FOR rid IN (
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10])
) AS pivot_table;
select
first_name, last_name, id_no, dob, state, county, age, race, ethnic1
, program_name,[income_amount],
[1] AS Colateral_1,
[2] AS Colateral_2,
[3] AS Colateral_3,
[4] AS Colateral_4,
[5] AS Colateral_5,
[6] AS Colateral_6,
[7] AS Colateral_7,
[8] AS Colateral_8,
[9] AS Colateral_9,
[10] AS Colateral_10
from #pvt
--select * from #test
drop table #test
drop table #pvt