SQL - Storing multiple records in a variable separated by commas

Question

I have two variables, 1 varchar named cust_ref, and 1 int named associated_ids. What I'm trying to accomplish is the following:

You provide cust_ref with a value which will usually result in between 1+ rows being returned from the Customer table. I am concerned with gathering all customer_id records for that cust_ref and storing them in the associated_ids variable seperated by commars.

This is the SQL I have so far, and obviously is only loading one of the customer_id records into the variable. Based on this example I would like select @associated_ids to return the following 75458,77397,94955

declare @cust_ref varchar(20) = 'ABGR55532'
declare @associated_ids int

select distinct @associated_ids = customer_id
from dbo.Customer
where cust_ref = @cust_ref

select @associated_ids

select *
from dbo.Customer
where cust_ref = @cust_ref

Here is the results from the above, as you can see there are actually 3 associated_ids that I need stored in the variable in this example but my command is capturing the largest, I want all 3 seperated by commars.

enter image description here

possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — DaveShaw, Jul 26 '13 at 14:14
I'm not sure why you would want anything to result in a comma separated string in SQL. If that is how it is presented in the UI, select out the rows and convert it in your DAL — Fiona - myaccessible.website, Jul 26 '13 at 14:14
I am creating a stored proc which will only need the cust_ref variable so that I can use IN (@associated_ids) — JsonStatham, Jul 26 '13 at 14:17
Also, I have had a look at the 'duplicates' and am struggling to make use of the FOR XML PATH syntax in my example, hence I would appreciate anyone providing the script for my example — JsonStatham, Jul 26 '13 at 14:32

score 0 · Accepted Answer · answered Jul 26 '13 at 14:38

0

declare @cust_ref varchar(20) = 'ABGR55532' --from your code
DECLARE @result varchar(100)

set @result =
(SELECT distinct (customer_id + ' ')
FROM dbo.Customer 
where cust_ref = @cust_ref --from your code
ORDER BY (customer_id + ' ')
FOR XML PATH (''))

SELECT REPLACE(RTRIM(@result),' ',',')

answered Jul 26 '13 at 14:38

Arghya C

9,805
2
47
66

This is good but bringing back 754587739794955, which is correct just with no comma's – JsonStatham Jul 26 '13 at 14:44
Please check the spaces. Only PATH('') doesnt have space, others have space in between. What I'm getting at first is like - val1 val2 val3 - with a space at end. Then I'm right trimming the last space, finally replacing spaces with commas. So, it should work! – Arghya C Jul 26 '13 at 14:48
1

Posted fixed query, please test that. Int columns were giving trouble. – Arghya C Jul 26 '13 at 14:57

score 0 · Answer 2 · answered Jul 26 '13 at 14:41

You could try something like this ... obviously, some adjustment will be needed:

create table x (id varchar(50),num int)
insert into x (id,num) values ('75458','20')
insert into x (id,num) values ('77397','20')
insert into x (id,num) values ('94955','20')

and then,

create function GetList (@num as varchar(10)) 
returns varchar(100) 
as 
begin
  declare @List varchar(100) 
  select @List = COALESCE(@List + ', ', '') + id
  from x 
  where num = @num

  return @List

end

Then, use something like this to get the values:

select distinct num,dbo.GetList(num) from x

Arghya C · Answer 3 · 2013-07-26T15:03:17.603

0

Here you go

DECLARE @cust_ref varchar(20) = 'ABGR55532' --from your code
DECLARE @result varchar(100)

set @result =
(SELECT distinct (cast(customer_id as varchar) + ' ')
FROM dbo.Customer 
where cust_ref = @cust_ref --from your code
ORDER BY (cast(customer_id as varchar) + ' ')
FOR XML PATH (''))

SELECT REPLACE(RTRIM(@result),' ',',')

edited Jul 26 '13 at 15:03

answered Jul 26 '13 at 14:56

Arghya C

9,805
2
47
66

SQL - Storing multiple records in a variable separated by commas

3 Answers3