Outer apply /xml path solution for rows to column problem

problem : I need to get the max of a table grouped by a value - but - when there is existing joined data in another table I want to join on those values too, even when the relationship is 1 to many;if there are 2 values joined - group on those 2 as if they were pivoted to a column value

this is the problem :

select MAX(main.id),main.data,side.data   from

(

select 1 as id ,'a'  data ,'aa' joindata

union select 2 as id ,'a'  data ,'ab' joindata

union select 1 as id ,'b'  data ,'ba' joindata

union select 2 as id ,'b'  data ,'bb' joindata

union select 3 as id ,'b'  data ,'bc' joindata

) main

left join

(

select  'aa' joindata , null data

union select 'ba' joindata , 'a' data

union select 'bb' joindata , 'a' data

union select 'bb' joindata , 'b' data

union select 'bc' joindata , 'b' data

) side

on side.joindata = main.joindata

group by main.data,side.data

these are the results

1-b-ba is erroneously missing because bb is being grouped on both a and b

This is the same exact query with outer apply

select MAX(main.id),main.data,side.data   from

(

select 1 as id ,'a'  data ,'aa' joindata

union select 2 as id ,'a'  data ,'ab' joindata

union select 1 as id ,'b'  data ,'ba' joindata

union select 2 as id ,'b'  data ,'bb' joindata

union select 3 as id ,'b'  data ,'bc' joindata

) main

outer apply

(

select  * from

 (

select  'aa' joindata , null data

union select 'ba' joindata , 'a' data

union select 'bb' joindata , 'a' data

union select 'bb' joindata , 'b' data

union select 'bc' joindata , 'b' data

 ) temp

 where temp.joindata = main.joindata

) side

group by main.data,side.data

the solution

select MAX(main.id) as maxid,main.data,side.data   from

(

select 1 as id ,'a'  data ,'aa' joindata

union select 2 as id ,'a'  data ,'ab' joindata

union select 1 as id ,'b'  data ,'ba' joindata

union select 2 as id ,'b'  data ,'bb' joindata

union select 3 as id ,'b'  data ,'bc' joindata

) main

outer apply

(

select temp.data + ''  from

 (

select  'aa' joindata , null data

union select 'ba' joindata , 'a' data

union select 'bb' joindata , 'a' data

union select 'bb' joindata , 'b' data

union select 'bc' joindata , 'b' data

 ) temp        

where temp.joindata = main.joindata

and temp.data is not null

FOR XML PATH('')

) as side(data)