Create an array from query results

[primagic]

I have the following info in a table:

RoomID Bed Type
1 Single
1 Double
1 Single
2 Single

How would I write a select query to return an array like:

RoomID Beds
1 2 x Single, 1 x Double (preferred)
1 Single, Double, Single (or if easier)
2 1 x Single

 

[gk53]

try that

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

drop table #rooms