-2

How to concatenate the result values into a single row?

I created two tables like this.

first table

clientid name
1        test1
2        test2
3        test3

second table

id   clientid
3    1
3    2
3    3

my requirement getting 

result
3  test1,test2,test3

My SP....

ALTER PROCEDURE [dbo].[usp_test]-- 3
    @ID INT
AS  
BEGIN
    SELECT CO.id,AR.name FROM dbo.test1 AR
    LEFT OUTER JOIN dbo.test2 CO On AR.clientid=CO.clientid
    WHERE CO.id=@ID
END

Then I am getting result as

id  name
3   test1
3   test2
3   test3

Please modify my sp to get the below format output

id  name
3   test1,test2,test3
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
user1237131
  • 1,853
  • 3
  • 26
  • 35
  • 2
    possible duplicate of [How to concatenate all strings from a certain column for each group](http://stackoverflow.com/questions/6579440/how-to-concatenate-all-strings-from-a-certain-column-for-each-group) – Kirill Polishchuk Mar 24 '12 at 11:24

1 Answers1

0
SELECT id, names = STUFF((
    SELECT N',' + name FROM dbo.test2
    WHERE clientid = x.clientid 
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')
FROM dbo.test1 AS x
WHERE id = @ID
GROUP BY id;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490