DECLARE @tTable TABLE
(
[Name] VARCHAR(50),
[Type] VARCHAR(50)
)
INSERT INTO @tTable([Name],[Type])
VALUES ('A','cust'),('A','officer'),('B','cust'),('B','officer'),('B','worker')
SELECT DISTINCT
T.Name
,STUFF(L.[Types],1,2,'') AS [Type]
FROM @tTable AS T
CROSS APPLY
(SELECT '::' + Tp.[Type]
FROM @tTable AS Tp
WHERE T.Name =Tp.Name
FOR XML PATH('')
) AS L ([Types])