First, I am assuming that you confused 'BB' and 'CC' in your sample data and query.
No, that is what I wanted just the casenumbers.
Really? Your sample query includes this WHERE clause:
where case like '%BB%'
And yet your sample data includes this 'case':
cc-109090
Please explain how your WHERE clause can ever select this data.
Moving on ...
Try running this. Should give you an idea how to get the data you want:
with data
as (
select name = 'A'
,cse = 'AA-109090'
union
select name = 'A'
,cse = 'CC-109090'
)
select AA = max(case when d.cse like 'AA%' then d.cse
else ''
end)
,CC = max(case when d.cse like 'CC%' then d.cse
else ''
end)
from data d
group by d.name
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.