SQL Server: how to generate comma separated string for distinct values in aggregated columns in group by statement

Question

I would like to generate a comma separated string for the distinct occurrences for an aggregated column in a group by statement. So I have:

Select Column1, ???statement involving Column2??? from MyTable
group by Column1

I can't figure out what to put between the questions marks.

Answer 1

Assuming that You have got the following tables:

use TSQL2012

    if object_id('testStackOverflow') is not null drop table testStackOverflow
    create table testStackOverflow(
    column1 varchar(100) not null,
    column2 varchar(100) not null,
    constraint PK2 primary key(column1,column2)

    )

    insert into testStackOverflow(column1,column2)
    values 
    ('value1','t1'),
    ('value1','t2'),
    ('value1','t3'),
    ('value2','t5'),
    ('value2','t6'),
    ('value2','t7')

You can use the following query:

   Select column1, 
    substring(
        (
            Select ','+sto2.column2  AS [text()]
            From testStackOverflow as sto2
            Where sto2.column1 = sto1.column1
            ORDER BY sto2.column1
            For XML PATH ('')
        ), 2, 1000) as column2concat
From testStackOverflow as sto1
group by column1

And the result will be :

I was using answer made by Ritesh as a reference: Concatenate many rows into a single text string?