Get Value separated by comma from the stored Procedure

Question

I have a table in sqlserver

UserID(PK)    Name    PhoneNumber
1             Test     123456
2             Test1    356456

And another table having FK of above table

 ID    RequestID   UserID(FK)
  1      20123        1
  2      20245        1
  3      21545        2

I need results as below

UserID    Name      phoneNumber  RequestID
 1        Test      123456       20123,20245
 2        Test1      356456       21545 

I had used the join but that gives multiple records for each row but I need result as above. Can anybody help how I should get this type of output?

Answer 1

SELECT [UserID]
     , [Name]
     ,[PhoneNumber]
     , stuff((SELECT distinct ' ,'+ CAST(RequestID AS VARCHAR)
         FROM [Request] 
         WHERE (UserID = [PK].UserID) 
         FOR XML PATH ('')),1,2,''
      ) AS Request1
FROM [PK]

I borrowed some concept from here

SQL Fiddle

Answer 2

There are a few different ways that this could be done.

Using FOR XML PATH and STUFF:

select u.userid,
  u.name,
  u.phonenumber,
  STUFF((SELECT distinct ', ' + cast(r.requestid as varchar(10))
         from requests r
         where u.userid = r.userid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') requests
from users u;

See SQL Fiddle with Demo

Or you can use CROSS APPLY and FOR XML PATH:

select u.userid,
  u.name,
  u.phonenumber, 
  left(r.requests, len(r.requests)-1) requests
from users u
cross apply
(
  select cast(r.requestid as varchar(10)) + ', '
  from  requests r
  where u.userid = r.userid
  FOR XML PATH('')
) r (requests);

See SQL Fiddle with Demo