using cursor or not? - 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)

using cursor or not?

Author

Topic

yns.emre
Starting Member

11 Posts

Posted - 2010-04-13 : 09:05:19

imagine that i have a table like this :

Name Job
Ahmet Journalist
Ahmet Referee

i want a select query from this table without using cursor,like this result :

Name Jobs
Ahmet Journalist,Referee

can i do this without using cursor ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-13 : 09:15:34

[code]SELECT DISTINCT t.Name,
STUFF((SELECT ',' + Job FROM Table WHERE Name=t.Name FOR XML PATH('')),1,1,'')
FROM Table t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 09:15:45

Yes u can



create table #yourTable (name varchar(20),job varchar(20))



insert #yourTable

select 'Ahmet','Journalist'
union all select 'Ahmet','Referee'



select y1.name,stuff((select  ',' + Job  from #yourTable y2 where y1.name=y2.name for XML path('') ),1,1,'') as job from #yourTable y1
group by y1.name

drop table #yourTable

Credit goes to visakh for the above code :)

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 09:17:00

PBUH

yns.emre
Starting Member

11 Posts

Posted - 2010-04-13 : 09:21:39

visakh16 and idera,thanks for your replies,but think that i have 10000 rows in a table,i mean performance,what should i do,i can use cursor,or i can use your explanations ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-13 : 09:23:55

why dont you test it out and see for yourself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-13 : 09:25:44

10000 rows is nothing in SQL Server

And I bet that a cursor is slower.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources