Concatenating fields - 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

General SQL Server Forums

New to SQL Server Programming

Concatenating fields

Author

Topic

azrael211
Starting Member

2 Posts

Posted - 2011-08-09 : 09:14:14

I am trying to get all the member of 1 team returned in a single field without having to use a cursor my current data set looks like this.

TeamID Player
1 Alasdair
2 Adam
3 Alasdair
4 Adam
3 Robert
4 Tim

I would like the output to show as below whihc concatenaets the plaers into a single field:

TeamID Players
4 Adam,Tim

Does anyone have any smart ideas or can point me in the right direction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 09:50:40

[code]SELECT t.teamID,
STUFF((SELECT ',' + Player FROM Table WHERE teamID = t.TeamID ORDER BY Player FOR XML PATH('')),1,1,'') AS players
FROM (SELECT DISTINCT TeamID FROM table)t
[/code]

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

azrael211
Starting Member

2 Posts

Posted - 2011-08-09 : 09:58:34

quote:

Originally posted by visakh16

SELECT t.teamID,
STUFF((SELECT ',' + Player FROM Table WHERE teamID = t.TeamID ORDER BY Player FOR XML PATH('')),1,1,'') AS players
FROM (SELECT DISTINCT TeamID FROM table)t

Thank you very much, I will have a play with that

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 09:58:59

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources