combining multiple rows - 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

combining multiple rows

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2012-11-23 : 05:16:40

Hello,

I have a delima, and im not really sure if this possible. But i have a table like lets say

id | data1
1 this
2 that
3 stuff

i want to be able to return this as one row with the data from data1 in one column inside colons each seperated by commas.

so the result would be

1 Column
'this', 'that', 'stuff'

can anyone help me with this.

Thank you,

rams

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-23 : 06:43:58

DECLARE @tab1 TABLE(id int, data1 varchar(10))
INSERT INTO @tab1 VALUES(1, 'this'),(2, 'that'),(3, 'stuff')

--Solution1:
DECLARE @a varchar(1000)
SELECT @a = COALESCE(@a + ', ', '') + data1
FROM @tab1
SELECT @a

--Alternate is:

SELECT id, STUFF((SELECT ',' + data1 FROM @tab1 FOR XML PATH('')),1,1,'') AS CSV
FROM @tab1

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources