/*
Column A | Column B
1 a 
1 b
2 c
2 d
*/
 
IF OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE Table1
CREATE TABLE Table1
(
  ColumnA INT,
  ColumnB VARCHAR(1)
)
 
GO
 
INSERT INTO Table1 (ColumnA, ColumnB) VALUES (1, 'a')
INSERT INTO Table1 (ColumnA, ColumnB) VALUES (1, 'b')
INSERT INTO Table1 (ColumnA, ColumnB) VALUES (2, 'c')
INSERT INTO Table1 (ColumnA, ColumnB) VALUES (2, 'd')
 
GO
 
GO
 
IF OBJECT_ID('dbo.JoinString', 'FN') IS NOT NULL DROP FUNCTION dbo.JoinString
 
GO
 
CREATE FUNCTION dbo.JoinString (@ColumnA INT)
RETURNS VARCHAR(100)
AS
BEGIN
 
  DECLARE @result VARCHAR(100)
  SET @result = ''
  SELECT @result = @result + ' ' + IsNull(ColumnB, '')
  FROM Table1
  WHERE ColumnA = @ColumnA
  ORDER BY ColumnB
 
  return LTRIM(@result)
END
 
GO
 
DECLARE @Tab Table
(
  ColumnA INT,
  ColumnB VARCHAR(100)
)
 
INSERT INTO @Tab (ColumnA) SELECT DISTINCT(ColumnA) FROM Table1 (NOLOCK)
UPDATE @Tab SET ColumnB = dbo.JoinString(ColumnA)
 
 
SELECT *
FROM @Tab