Combine column values in a single field

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)

Combine column values in a single field

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2008-10-24 : 09:22:58

I am looking for a simple way to take values from a temp table and combine them in a single field seperated by a comma. Without cycleing through them and adding them to the field.

Example:
Declare @teammembers varchar(2000)

(select from Table dbo.#teammembers.team)

Joe Blow
John smith
Carol chaning
Art garfunkle
kermit the frog

Selet @teammembers

out
Joe Blow, John smith, Carol chaning, Art garfunkle ,kermit the frog

Jim
Users <> Logic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 09:28:28

If you want to show data in front end application, do concatenation there

declare @members varchar(8000)
select @members =coalesce(@members+',','')+member from #teammembers
print @members

Madhivanan

Failing to plan is Planning to fail

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2008-10-24 : 11:15:54

Ahhh Yes coalesce.

Never had a need for it so I forgot all about it.

Thanks for the brain jump start.

Jim
Users <> Logic

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2008-10-25 : 00:22:09

If you are on 2005 using XML is more efficient.

DECLARE @Members nvarchar(max)

DECLARE @Member TABLE
(
MemberName nvarchar(100)
)

INSERT INTO @Member VALUES ('Joe Blow')
INSERT INTO @Member VALUES ('John smith')
INSERT INTO @Member VALUES ('Carol chaning')
INSERT INTO @Member VALUES ('Art garfunkle')

SELECT @Members =

STUFF(

(
SELECT ',' + MemberName
FROM @Member
FOR XML PATH('')
)

,1, 1, '')

SELECT @Members

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 07:12:39

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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources