How to concatenate data where rows are the same?

Question

I have a table with duplicate values in Column1 and unique values in Column2. How can I concatenate text in Column2 where duplicate values are found in Column1, without deleting duplicates.

An example of my table structure:

+-----------+-----------+
|  Column1  |  Column2  |
+-----------+-----------+
|  Employ1  |  Values1  |
+-----------+-----------+
|  Employ1  |  Values2  |
+-----------+-----------+
|  Employ2  |  Values3  |
+-----------+-----------+
|  Employ3  |  Values4  |
+-----------+-----------+

The desired output required:

+-----------+--------------------+
|  Column1  |       Column2      |
+-----------+--------------------+
|  Employ1  |  Values1, Values2  |
+-----------+--------------------+
|  Employ1  |  Values1, Values2  |
+-----------+--------------------+
|  Employ2  |      Values3       |
+-----------+--------------------+
|  Employ3  |      Values4       |
+-----------+--------------------+

I have no idea how to do this, so unfortunately cannot provide code of what I already have.

Answer 1

Use LEFT JOIN for SQLServer in combination with string_agg function

select T.column1, A.Column2
from TestT T
left join 
(select column1, string_agg(Column2, ',') Column2
from TestT
group by column1) A
on T.column1 = A.column1

Here you can see the DEMO

For an older version of SQLSERVER (From SQLServer 2017 I believe the string_agg function is available...) try to create function:

CREATE FUNCTION dbo.udf_select_concat ( @c varchar(50) )
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
       SET @p = '' ;
    SELECT @p = @p + Column2 + ' '
      FROM TestT
     WHERE column1 = @c ;
RETURN @p
END;

And then call it like this:

SELECT column1, replace(rtrim(dbo.udf_select_concat( column1 )), ' ', ',') Column2
FROM TestT
GROUP BY column1 ;

Here is the DEMO