This can be only possible in this way
DECLARE @Table TABLE (Column1 VARCHAR(10), Column2 VARCHAR(10))
INSERT INTO @Table VALUES ('A','1')
INSERT INTO @Table VALUES ('A','2')
INSERT INTO @Table VALUES ('B','1')
INSERT INTO @Table VALUES ('B','2')
SELECT DISTINCT Column1
,LEFT((SELECT DISTINCT CAST(Column2 + ', ' AS VARCHAR(MAX))
FROM @Table
WHERE Column1 = Column1
FOR XML PATH ('')),
LEN((SELECT DISTINCT CAST(Column2 + ', ' AS VARCHAR(MAX))
FROM @Table
WHERE Column1 = Column1
FOR XML PATH (''))) - 2) AS Column2
FROM @Table
Output
Column1 Column2
A 1, 2
B 1, 2