Comma Separated List from SQL Grouping

Question

I'm trying to build comma separated list per group in sql, As am using Parallel Data Warehouse i need to do this without using FOR XML or recursive function ( not supported ).

any other way to achieve this ?

Input:  
ID  Value
1   2
1   3
1   4
2   1
2   10

Output: 
ID  List
1   2,3,4
2   1,10

Answer 1

This will not perform well at all so I recommend you use some other solution (like a SQL Server linked server to PDW) if you need performance. But I believe this should work on PDW:

declare @ID int = (select min(ID) from tbl);
declare @Value int = -1;
declare @concat varchar(8000) = '';

create table #tmp (
 ID int,
 [concat] varchar(8000)
)
with (distribution=hash(ID), location=user_db);

while @ID is not null
begin
    set @Value = (select min([Value]) from tbl where ID = @ID and [Value]>@Value);
    if @Value is not null
        set @concat = @concat + case when @concat = '' then '' else ',' end + cast(@Value as varchar(8000));
    else
    begin
        insert #tmp (ID, [concat])
        values (@ID, @concat);

        set @ID = (select min(ID) from tbl where ID > @ID);
        set @Value = -1;
        set @concat = '';
    end
end

select * from #tmp;

drop table #tmp;