SQL: Concatenate Column Values

Sample 1 - Concatenate all values into one comma-separated value

Sample table: #tmp

Prod
123
124
125

declare @ProductIDs varchar(max)
select distinct @ProductIDs = stuff(isnull((select ',' + prod from #tmp x
for xml path (''), type).value('.','varchar(max)'), ''), 1, 1, '')
from #tmp t

print @ProductIDs

Output:

123,124,125

Sample 2 - Concatenate values into groups of comma-separated values

Sample table: #tmp
id1     Prod
1        127
1        128
2        587
2        987

select id1, stuff(isnull((select ',' + prod

from #tmp x where x.id1 = t.id1

group by x.prod

for xml path (''), type).value('.','varchar(max)'), ''), 1, 1, '')  'Product IDs'

from #tmp t
group by id1

Output:
id1    Product IDs
1       127,128
2       587,987