create table #rooms
(
RoomID INT,
BedType varchar(20)
)
insert into #rooms
select
1, 'Single'
union all
select
1, 'Double'
union all
select
1, 'Single'
union all
select
2, 'Single'
select BedType,
stuff(
(
select ','+ cast(RoomID as varchar(10)) from #rooms where BedType = t.BedType for XML path('')
),1,1,'')
from (select distinct BedType from #rooms)t
order by BedType desc