Something like this should work.

Notice how I posted ddl and sample data in a consumable format? That is something you should do in the future. It makes it easy for us volunteers to work on your issue. It will also help eliminate the need for you to beg us for help. 😎

create table #Something

(

Model_No int,

ModelName varchar(25)

)

insert #Something

select 100, 'Zirrard' union all

select 100, 'Modached'

select Model_No, COUNT(*), case when COUNT(*) > 1 then 'Multi Model' else 'Single Model' end as ModelType,

STUFF((select ', ' + ModelName

from #Something s2

where s2.Model_No = s.Model_No

order by s2.ModelName

for XML PATH('')), 1, 1, ' ')

from #Something s

group by Model_No

drop table #Something