with test(sno,snum,ssex,sre) as ( select 'a',1,'m','xx' union all select 'a',2,'fm','xxx' union all select 'a',3,'fm','xxxxx' union all select 'b',2,'fm','x' union all select 'b',3,'m','xxxx' union all select 'b',4,'fm','xx' union all select 'c',8,'m','xxx' union all select 'c',9,'fm','xxxx' )select sno,MIN(snum) as snum,stuff((select ' '+sre from test where a.sno=sno for XML path('')),1,1,'') as sre
from test as a group by a.sno
/* sno snum sre --------------------- a 1 xx xxx xxxxx b 2 x xxxx xx c 8 xxx xxxx ---------------------- */
(
select 'a',1,'m','xx' union all
select 'a',2,'fm','xxx' union all
select 'a',3,'fm','xxxxx' union all
select 'b',2,'fm','x' union all
select 'b',3,'m','xxxx' union all
select 'b',4,'fm','xx' union all
select 'c',8,'m','xxx' union all
select 'c',9,'fm','xxxx'
)select sno,MIN(snum) as snum,stuff((select ' '+sre from test where a.sno=sno for XML path('')),1,1,'') as sre
from test as a group by a.sno
/*
sno snum sre
---------------------
a 1 xx xxx xxxxx
b 2 x xxxx xx
c 8 xxx xxxx
----------------------
*/