create table #t1 (ITem_no int)

insert into #t1 (Item_no) values (1)

insert into #t1 (Item_no) values (2)

insert into #t1 (Item_no) values (3)

create table #t2 (ITem_no int, format varchar(16))

insert into #t2 (Item_no, format) values (1, 'Soap')

insert into #t2 (Item_no, format) values (1, 'Pen')

insert into #t2 (Item_no, format) values (1, 'Pencil')

insert into #t2 (Item_no, format) values (1, 'Eraser')

insert into #t2 (Item_no, format) values (2, 'Soft Toys')

insert into #t2 (Item_no, format) values (2, 'Soft Toys')

insert into #t2 (Item_no, format) values (2, 'Adult Toys')

insert into #t2 (Item_no, format) values (2, 'Kids Items')

select

t1.*,

(

select t2.format + ', '

from #t2 t2

where t2.Item_no = t1.Item_no

order by t2.format

for xml path('')

)

from #t1 t1