with R as (select LC.MEMBER, CT.COLLATERAL_ID, row_number() over(partition by LC.MEMBER order by CT.COLLATERAL_ID) as rnfrom dbo.Table1 LC inner join dbo.Table2 as CT on LC.COLLATERAL_ID = CT.COLLATERAL_ID)

SELECT MEMBER ,max(case when rn = 1 then COLLATERAL_ID end) as Collateral_1 ,max(case when rn = 2 then COLLATERAL_ID end) as Collateral_2 ,max(case when rn = 3 then COLLATERAL_ID end) as Collateral_3 ,max(case when rn = 4 then COLLATERAL_ID end) as Collateral_4 ,max(case when rn = 5 then COLLATERAL_ID end) as Collateral_5 ,max(case when rn = 6 then COLLATERAL_ID end) as Collateral_6 ,max(case when rn = 7 then COLLATERAL_ID end) as Collateral_7 ,max(case when rn = 8 then COLLATERAL_ID end) as Collateral_8 ,max(case when rn = 9 then COLLATERAL_ID end) as Collateral_9 ,max(case when rn = 10 then COLLATERAL_ID end) as Collateral_10from Rgroup by MEMBER

So each collateral "could" have a title. I need to keep it in one row, but I am trying to do is add another column with a title number for each collateral (Null if there isn't one) from table3 where collateral_IDs match. I have tried unsuccessfully to do this and get the correct title number matches.

SELECT MEMBER ,max(case when rn = 1 then COLLATERAL_ID end) as Collateral_1 ,max(case when rn = 1 then TITLE end) as TITLE_1 ,max(case when rn = 2 then COLLATERAL_ID end) as Collateral_2 ,max(case when rn = 1 then TITLE end) as TITLE_2...

select collateral_id, count(distinct title) from LCollat group by collateral_id having count(distinct title) > 1and/or vice-versaselect title, count(distinct collateral_id) from LCollat group by title having count(distinct collateral_id) > 1