Combine field values from different rows

Question

I'm searching for an intelligent method to aggregate the value of a field over different rows. Lets say this is my table:

ID           Value
1            TextA
1            TextB
1            TextC
2            TextA
2            TextC

The result I need to get would be:

ID           Values
1            TextA,TextB,TextC
2            TextA,TextC

Currently I'm working with a cursor. I think this solution is very procedural (thats where I come from) and I was wondering if there wasn't a set-theoratic solution. This is my cursor code:

-- Basic table containing positions
CREATE TABLE #out (id int, [values] nvarchar(1000))

INSERT INTO #out
select     1, 'TextA'
UNION ALL
select     1, 'TextB'
UNION ALL
select     1, 'TextC'
UNION ALL
select     2, 'TextA'
UNION ALL
select     2, 'TextC'

declare @id_old nvarchar(70) = NULL
declare @values nvarchar(2000) = ''
declare @id nvarchar(70)
declare @value nvarchar(50)

declare ccc cursor fast_forward for
SELECT id, [values]
from #out
order by id

open ccc

fetch next from ccc into @id, @value

while @@FETCH_STATUS = 0
begin
    if @id_old is null
    begin
        set @id_old = @id
    end

    -- check if new id or same as previous iteration
    if @id = @id_old
    begin
        if LEN(@values) = 0
        begin
            set @values += @value -- set initial value
        end
        else
        begin
            set @values += ',' + @value -- add additional values
        end
    end

    fetch next from ccc into @id, @value

    -- when id switches update input table with collection of values
    if @id <> @id_old or @@FETCH_STATUS <> 0
    begin
        update #out set [values] = @values where id = @id_old

        set @id_old = @id
        set @values = ''
    end
end

close ccc
deallocate ccc

-- since every id gets a collection of all values only keep the first entry
delete tblOut
from  (select id, [values], ROW_NUMBER() over (partition by id, [values] order by id, [values]) RowNumber from #out) tblOut
where tblOut.RowNumber > 1

Answer 1

SELECT ID ,
       STUFF(
               (SELECT ',' + Value
                FROM myTable a
                WHERE b.ID = a.ID
                GROUP BY a.Value
                ORDER BY a.Value
                FOR XML PATH('')), 1 , 1, '')
VALUESS
FROM myTable b
GROUP BY ID;