USE tempdb
IF OBJECT_ID('dbo.CONsum', 'P') IS NOT NULL
  DROP PROC dbo.CONsum;
GO

CREATE FUNCTION dbo.CONsum(@id INTEGER)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''
SELECT @Result = @Result + companyname + ', ' 
                      FROM (SELECT companyname, empid
FROM TestSQL.Sales.Orders AS SO
															 ,TestSQL.Sales.Customers AS SC
													WHERE SO.custid=SC.custid) AS rez1
												WHERE empid = @id;
RETURN '"' + RTRIM(@Result) + '"'
END
GO

SELECT DISTINCT empid, dbo.CONsum(empid) FROM TestSQL.Sales.Orders
GO

USE tempdb
IF OBJECT_ID('dbo.CONsum', 'F') IS NOT NULL
  DROP PROC dbo.CONsum;
GO

CREATE FUNCTION dbo.CONs(@id INTEGER)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''
SELECT @Result = @Result + companyname + ', '
                FROM (SELECT distinct companyname, SO.empid
				FROM TestSQL.Sales.Orders AS SO
			                 ,TestSQL.Sales.Customers AS SC
						WHERE SO.custid=SC.custid
			   ) AS qqq
		WHERE empid = @id;
RETURN '"' + RTRIM(@Result) + '"'
END
GO

SELECT DISTINCT empid, dbo.CONs(empid) FROM TestSQL.Sales.Orders
GO

SELECT town, 
    STRING_AGG (email, ';') WITHIN GROUP (ORDER BY email ASC) AS emails 
FROM dbo.Employee 
GROUP BY town; 

use tempdb
go
drop table test
go
create table dbo.TEST ([Тип] INTEGER, [Имя] NVARCHAR(100), [Количество] INTEGER)
go
INSERT dbo.TEST VALUES(1, N'Молоко', 5)
INSERT dbo.TEST VALUES(1, N'Сметана', 6)
INSERT dbo.TEST VALUES(2, N'Гвозди', 44)
INSERT dbo.TEST VALUES(3, N'Машины', 1)

select [Тип] ,( select [Имя]+': '+cast([Количество] as nvarchar)+ ',' as 'data()' from test t2 where t1.[Тип]=t2.[Тип] for xml path('') )
from dbo.TEST t1
group by [Тип]

use tempdb
go
drop table test
go
create table dbo.TEST ([Тип] INTEGER, [Имя] NVARCHAR(100), [Количество] INTEGER)
go
INSERT dbo.TEST VALUES(1, N'Моло<ко', 5)
INSERT dbo.TEST VALUES(1, N'Сметана', 6)
INSERT dbo.TEST VALUES(2, N'Гвоз>ди', 44)
INSERT dbo.TEST VALUES(2, N'М<ашины', 1)

select [Тип] ,( select [Имя]+': '+cast([Количество] as nvarchar)+ ',' as 'data()' from test t2 where t1.[Тип]=t2.[Тип] for xml path('') )
from dbo.TEST t1
group by [Тип]

1	Моло<ко: 5, Сметана: 6,
2	Гвоз>ди: 44, М<ашины: 1,

SELECT [Тип], STUFF((SELECT N', '+[Имя]+N': '+CAST([Количество] AS NVARCHAR) FROM dbo.TEST t2 WHERE t1.[Тип]=t2.[Тип] FOR XML PATH(''),TYPE).value('.','NVARCHAR(100)'),1,2,N'')
FROM dbo.TEST t1
GROUP BY [Тип];