T-SQL Statement for a problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL Statement for a problem

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-11-20 : 00:23:59

Hi,

SELECT * from Stud_Places

RolNo Place
-----------------
1 Dubai
1 Kuwait
1 Paris
2 London
2 Texas
3 Los Angels
3 Germany
3 France

I want the result to be like this

RolNo Places
------------------
1 Dubai , Kuwait , Paris
2 London , Texas
3 Los Angels , Germany , France

can any one suggest a T-SQL statement for this ???

Regards

ARV

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-20 : 00:47:45

Try this

declare @tbl as table(rollno int,places varchar(20))
insert into @tbl
select 1,'Dubai' union all
select 1,'Kuwait' union all
select 1,'Paris' union all
select 2,'London' union all
select 2,'Texas'

select rollno, (select places + ',' from @tbl t1 where t1. rollno =t2. rollno
for xml path(''))from @tbl t2 group by rollno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 01:15:02

slight modification to remove extra , in end


select rollno, stuff((select ','+ places from @tbl t1 where t1. rollno =t2. rollno
for xml path('')),1,1,'')from @tbl t2 group by rollno

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 02:12:53

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources