SQL select list as comma separated values

Question

I have a table with INT Id column. I need to select Ids as comma separated values in a subquery.

I tried:

DECLARE @ids VARCHAR(MAX)
SELECT @ids= COALESCE(@ids +',' ,'') + Convert(nvarchar(8), Id)
FROM TableX

SELECT @ids

It works but the problem is that I cannot use declared variable. Is there a way to avoid variable use?

Example:

SELECT Id FROM TableX

Result:

I want:

1,2,5,7

May I ask what is the larger problem you are trying to solve? I get the sense there may be some unnecessary gymnastics going on. — Dave Mason, Jun 03 '16 at 19:57
@DMason you may be right. basically I have CTE with date ranges and for each date range I need to select a list of items. So pretty much I need to return hierarchical data from database. — Andrei, Jun 03 '16 at 19:59
If it is hierarchical data, maybe selecting data FOR XML would be more appropriate? That spins things off in a different direction, though. Might not be workable, depending on how far along you've gotten. — Dave Mason, Jun 03 '16 at 20:06
@AndreiM: Could be more helpful if query returns list of items and for every item the rangeid or rangenum (two columns: ItemId, RangeId or three columns: ItemId, ItemName and RangeId) ? — Bogdan Sahlean, Jun 03 '16 at 20:06
If we concat items for every range at database level then on client side (C#) those lists of items should be splited (I suppose). — Bogdan Sahlean, Jun 03 '16 at 20:09

Valentin Petkov · Answer 1 · 2016-06-03T20:10:28.507

4

try this

 DECLARE  @query nvarchar(max)

 select @query = STUFF(  (SELECT N',' + cast( id as nvarchar)  
 from TableX 

 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'')

 select  @query

NO variable :

  select   STUFF(  (SELECT N',' + cast( id as nvarchar)  
  from TableX 
  FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'')

edited Jun 03 '16 at 20:10

answered Jun 03 '16 at 20:06

Valentin Petkov

1,570
18
23

"the problem is that I cannot use declared variable" – Nick Jun 03 '16 at 20:08
I add no variable case – Valentin Petkov Jun 03 '16 at 20:13
@ErikE, he was quoting the original question. – Joe Farrell Jun 03 '16 at 20:21

score 1 · Answer 2 · answered Jun 03 '16 at 20:02

You can create a function to put the code

create function dbo.csv ()
returns varchar(max)
as
begin
    DECLARE @ids VARCHAR(MAX)
    SELECT @ids= COALESCE(@ids +',' ,'') + Convert(nvarchar(8), Id)
    FROM TableX

    return @ids
end

And the use the fucntion in the context that you cannot use variables

select  dbo.csv()

Regards

SQL select list as comma separated values

2 Answers2